Sample 48638: Altering all character column lengths in a table to be equal to the longest value in each column, without re-creating the table
 |  |  |  |  |  |
This sample finds the maximum length of each character value and uses the SQL procedure and the ALTER TABLE statement to alter each character column and assign the maximum length for value found. The intent of the sample is to reduce the size of the table without the need to re-create the table.
This sample uses 3 parameters that are not case-sensitive. The third parameter is optional.
- Supply the SAS Libref where the table is located.
- Supply the SAS table name.
- Supply a Y to add or alter any existing Informat or Format value. If no value is supplied, any existing Informat or Format remain unchanged.
Click the Full Code tab in this note to access the sample code.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This sample creates a SAS data set in the Work library with character lengths of 75. The character values
vary in length between observations. The sample determines the maximum character length for each column.
The sample uses the PROC SQL / Alter Table statement to alter the column lengths without the need to
recreate the table.
data test;
length var1 - var10 8 var11 - var15 $75 var16 - var20 8 var21 - var25 $75 ;
format var1 - var10 8. var11 - var15 $75. var16 - var20 8. var21 - var25 $75. ;
retain var1 - var10 0 var16 - var20 0;
var11='aaa';
var12='bbb bbbbb';
var13='c';
var14='ddddd';
var15='eeeee eee ee eee';
var21='ff';
var22='gggg';
var23='hhhhhh hhhhhhh hhhhhh hhhh';
var24='iiiiiiiiii';
var25='jjjjjjjjjjjjjjjj';
output;
var11='aaaaaaa';
var12='bb b bbbb bbb';
var13='cccccccc';
var14='ddddddddddd';
var15='e e e ee eee eee';
var21='fffffff';
var22='ggggggg gg ggggggg';
var23='hhhhhh hhhhhhhh hhhhhh hhhhhhhh hhhhhh hhhh hh';
var24='iiiiiiiiiiiiiiiiiiiiiii';
var25='jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj';
output;
proc contents data=test;
run;
/************************************************************/
/* this macro function alters all character column lengths */
/* */
/* the character column lengths will be assigned the length */
/* of the longest character value found in the column */
/************************************************************/
/************************************************************/
/* Macro Parameters */
/* */
/* Libref = libref where the table is located */
/* Memname = the table name that you want to alter */
/* Inf_fmt = Y - if you want Informats / Formats to be */
/* altered or added */
/************************************************************/
%macro chg_length(libref,memname,inf_fmt);
/************************************************************/
/* */
/* this step checks to see if the table is empty */
/* */
/* if the table is empty the table will not be altered */
/* */
/* if the table is empty the following message is written */
/* to the SAS Log: */
/* */
/* *** TABLE IS EMPTY. NO CHANGES OCCURRED *** */
/************************************************************/
proc sql noprint;
select nobs into :obs separated by ' '
from dictionary.tables
where libname = %upcase(%str(%'&libref.%'))
and memname = %upcase(%str(%'&memname.%'))
;
quit;
%if &obs ne 0 %then %do;
/************************************************************/
/* get the count of character columns we are dealing with */
/* DISTINCT ensures we get the right number of columns */
/* */
/* the "separated by" trims the &CT macro variable value */
/* so that the %LEFT() function is not required when using */
/* the macro variable */
/************************************************************/
proc sql noprint ;
select distinct count(*) into :ct separated by ','
from dictionary.columns
where libname = %upcase(%str(%'&libref.%'))
and memname = %upcase(%str(%'&memname.%'))
and type = 'char'
;
quit;
/************************************************************/
/* build a series of macro variables containing the */
/* character column names who's lengths we will be altering */
/* */
/* column name order will be based on the column position */
/* in the table */
/************************************************************/
options nonotes;
proc sql noprint;
select name into :name1 - :name&ct
from dictionary.columns
where libname = %upcase(%str(%'&libref.%'))
and memname = %upcase(%str(%'&memname.%'))
and type = 'char'
order by varnum ;
quit;
/* build the MAX(LENGTH()) expression for each column for the next step */
proc sql noprint;
select "max(length("||trim(name)||"))" into :maxlen1 - :maxlen&ct
from dictionary.columns
where libname = %upcase(%str(%'&libref.%'))
and memname = %upcase(%str(%'&memname.%'))
and type = 'char'
order by varnum ;
quit;
options notes;
/******************************************************************/
/* this step dynamically builds the SELECT list. the SELECT list */
/* contains an expression "MAX(LENGTH(xxxxx))" for each of the */
/* character columns. when the SELECT is executed it returns the */
/* max length of the character value in each column and inserts */
/* the lengths into a series of macro variables name LNx */
/* */
/* the LABEL= is used to assign a label to each of the columns */
/* so that if you remove the NOPRINT option you will see the */
/* length value associated with the column in the Output window */
/* or Output listing */
/* */
/* the LABEL= does not affect any labels that might already be */
/* be assigned to the character columns */
/* */
/* the logic here is: if not last CT then put a comma after each */
/* name on the SELECT list. if it is last CT then omit comma */
/* after the last name before the FROM clause */
/******************************************************************/
proc sql noprint;
select
%do i=1 %to &ct;
%if &i <= &ct-1
%then
&&maxlen&i label="&&name&i", ;
%else
&&maxlen&ct label="&&name&i" into ;
%end;
%do j=1 %to &ct;
%if &j <= &ct-1
%then
:ln&j, ;
%else
:ln&j ;
%end;
from &&libref..&memname;
quit;
/******************************************************************/
/* this step uses the PROC SQL ALTER Table statement to alter the */
/* character column lengths. */
/* */
/* it dynamically builds the list of column names and pairs with */
/* them the new length to be assigned to each column */
/* */
/* the new length should be equal to max length of the character */
/* value found in each column */
/* */
/* the logic here is: if not last CT then put a comma after each */
/* column and length pair. if it is last CT then omit comma after*/
/* the last column on the SELECT before the FROM clause */
/* */
/* if a 'Y' is supplied for the INF_FMT parameter then both the */
/* Informat and Format attributes will be changed to be equal to */
/* the column length */
/* */
/* if there are no Informat or Format attributes they will be */
/* added to the table */
/******************************************************************/
%if %upcase(&inf_fmt) = Y %then %do;
proc sql;
alter table &&libref..&memname
modify
%do i=1 %to &ct;
%if &i <= &ct-1
%then
&&name&i char(&&ln&i) informat= $%left(&&ln&i...) format= $%left(&&ln&i...) , ;
%else
&&name&i char(&&ln&i) informat= $%left(&&ln&i...) format= $%left(&&ln&i...) ;
%end;
;
quit;
%end;
/******************************************************************/
/* if the INF_FMT parameter is omitted then any existing Informat */
/* or Format will remain unchanged or will not be added */
/******************************************************************/
%if %upcase(&inf_fmt) ^= Y %then %do;
proc sql;
alter table &&libref..&memname
modify
%do i=1 %to &ct;
%if &i <= &ct-1
%then
&&name&i char(&&ln&i) , ;
%else
&&name&i char(&&ln&i) ;
%end;
;
quit;
%end;
%end;
%if &obs = 0 %then %do;
data _null_;
put / "*** TABLE IS EMPTY. NO CHANGES OCCURRED ***" /;
run;
%end;
%mend chg_length;
/******************************************************************/
/* invoke the macro and supply the SAS Libref, table name and a */
/* 'Y' (if you want Informats / Formats altered or added) */
/* */
/* For example: */
/* */
/* %chg_length(work,test,y); */
/* */
/******************************************************************/
%chg_length(work,test,y);
proc contents data=test;
run;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
* Contents of table TEST before Alter *
The CONTENTS Procedure
Data Set Name WORK.TEST Observations 2
Member Type DATA Variables 25
Engine V9 Indexes 0
Created Monday, December 10, 2012 02:52:26 PM Observation Length 872
Last Modified Monday, December 10, 2012 02:52:26 PM Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_64
Encoding wlatin1 Western (Windows)
Engine/Host Dependent Information
Data Set Page Size 16384
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 18
Obs in First Data Page 2
Number of Data Set Repairs 0
Filename C:\Users\AppData\Local\Temp\SAS Temporary
Files\_TD7648_D71134_\test.sas7bdat
Release Created 9.0301M2
Host Created X64_7PRO
Alphabetic List of Variables and Attributes
# Variable Type Len Format
1 var1 Num 8 8.
2 var2 Num 8 8.
3 var3 Num 8 8.
4 var4 Num 8 8.
5 var5 Num 8 8.
6 var6 Num 8 8.
7 var7 Num 8 8.
8 var8 Num 8 8.
9 var9 Num 8 8.
10 var10 Num 8 8.
11 var11 Char 75 $75.
12 var12 Char 75 $75.
13 var13 Char 75 $75.
14 var14 Char 75 $75.
15 var15 Char 75 $75.
16 var16 Num 8 8.
17 var17 Num 8 8.
18 var18 Num 8 8.
19 var19 Num 8 8.
20 var20 Num 8 8.
21 var21 Char 75 $75.
22 var22 Char 75 $75.
23 var23 Char 75 $75.
24 var24 Char 75 $75.
25 var25 Char 75 $75.
------------------------------------------------------------------------------------------------
* Contents of table TEST after Alter *
The CONTENTS Procedure
Data Set Name WORK.TEST Observations 2
Member Type DATA Variables 25
Engine V9 Indexes 0
Created Monday, December 10, 2012 02:52:26 PM Observation Length 320
Last Modified Monday, December 10, 2012 02:52:26 PM Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_64
Encoding wlatin1 Western (Windows)
Engine/Host Dependent Information
Data Set Page Size 16384
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 51
Obs in First Data Page 2
Number of Data Set Repairs 0
Filename C:\Users\AppData\Local\Temp\SAS Temporary
Files\_TD7648_D71134_\test.sas7bdat
Release Created 9.0301M2
Host Created X64_7PRO
Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat
1 var1 Num 8 8.
2 var2 Num 8 8.
3 var3 Num 8 8.
4 var4 Num 8 8.
5 var5 Num 8 8.
6 var6 Num 8 8.
7 var7 Num 8 8.
8 var8 Num 8 8.
9 var9 Num 8 8.
10 var10 Num 8 8.
11 var11 Char 7 $7. $7.
12 var12 Char 15 $15. $15.
13 var13 Char 8 $8. $8.
14 var14 Char 11 $11. $11.
15 var15 Char 25 $25. $25.
16 var16 Num 8 8.
17 var17 Num 8 8.
18 var18 Num 8 8.
19 var19 Num 8 8.
20 var20 Num 8 8.
21 var21 Char 7 $7. $7.
22 var22 Char 21 $21. $21.
23 var23 Char 49 $49. $49.
24 var24 Char 23 $23. $23.
25 var25 Char 31 $31. $31.
| Type: | Sample |
| Date Modified: | 2013-02-22 17:07:47 |
| Date Created: | 2012-12-10 09:20:00 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | N/A | z/OS | ||
| Z64 | ||||
| Macintosh | ||||
| Macintosh on x64 | ||||
| Microsoft Windows 2000 Professional | ||||
| Microsoft Windows Server 2003 for x64 | ||||
| Microsoft Windows Server 2008 for x64 | ||||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | ||||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | ||||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | ||||
| Microsoft Windows XP 64-bit Edition | ||||
| Microsoft® Windows® for x64 | ||||
| OS/2 | ||||
| Microsoft Windows 8 Pro | ||||
| Microsoft Windows 95/98 | ||||
| Microsoft Windows 2000 Advanced Server | ||||
| Microsoft Windows 2000 Datacenter Server | ||||
| Microsoft Windows 2000 Server | ||||
| Microsoft Windows NT Workstation | ||||
| Microsoft Windows Server 2003 Datacenter Edition | ||||
| Microsoft Windows Server 2003 Enterprise Edition | ||||
| Microsoft Windows Server 2003 Standard Edition | ||||
| Microsoft Windows Server 2008 | ||||
| Microsoft Windows Server 2012 | ||||
| Microsoft Windows XP Professional | ||||
| Windows 7 Enterprise 32 bit | ||||
| Windows 7 Enterprise x64 | ||||
| Windows 7 Home Premium 32 bit | ||||
| Windows 7 Home Premium x64 | ||||
| Windows 7 Professional 32 bit | ||||
| Windows 7 Professional x64 | ||||
| Windows 7 Ultimate 32 bit | ||||
| Windows 7 Ultimate x64 | ||||
| Windows Millennium Edition (Me) | ||||
| Windows Vista | ||||
| Windows Vista for x64 | ||||
| 64-bit Enabled AIX | ||||
| 64-bit Enabled HP-UX | ||||
| 64-bit Enabled Solaris | ||||
| ABI+ for Intel Architecture | ||||
| AIX | ||||
| HP-UX | ||||
| HP-UX IPF | ||||
| IRIX | ||||
| Linux | ||||
| Linux for x64 | ||||
| Linux on Itanium | ||||
| OpenVMS Alpha | ||||
| OpenVMS on HP Integrity | ||||
| Solaris | ||||
| Solaris for x64 | ||||
| Tru64 UNIX | ||||