DATA Step, Macro, Functions and more

How to change the length of multiple variables in one go in SAS?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How to change the length of multiple variables in one go in SAS?

[ Edited ]

Hi,

I have a requirement of changing length of multiple variables in one go in a sas dataset. I know this can be done by using 'RENAME' statement but I have to do this for over 70 variables. Is there a way to get this done in a much simpler way?


Accepted Solutions
Solution
‎12-14-2016 05:57 AM
Frequent Contributor
Posts: 136

Re: How to change the length of multiple variables in one go in SAS?

Using dictionary tables makes your task easier.
Instead of typing all the variable names it's easy to type the "VARNUM" numbers for the corresponding 
variables.

 

/* Here I have 10 variables and I need to change the length for only some of the variables */

data one;

infile datalines;

input AR1_ABC $3. AR2_ADA $3. AR3_ASD $3. AR4_ASDF $3.AR5_ASDEW $3. AR6_SDFJ $3. AR7_EWEK $3. AR8_ASKE $3. AR9_ASKWE $3. AR10_KJREI $3.;

datalines;

ABC BBB ASB AAS ATS EES DAS ASD FDA ASD

DAS AEW KJL ASD AES EWL SAG GLA GKE AKS

;

run;

/* Find the VARNUM numbers for the variables that you want to change the length from dictionary tables*/

PROC SQL;

select *

from dictionary.columns

where libname="WORK" and memname="ONE";

quit;

/* Put all the variable names that you want to change the length in a macro */

proc sql noprint;

select name into :names separated by " "

from dictionary.columns

where libname="WORK" and memname="ONE" and varnum in (1,5,6,9);

quit;

%put &names;

proc sql noprint;

select name into :names_retain separated by " "

from dictionary.columns

where libname="WORK" and memname="ONE" ;

quit;

%put &names_retain;

data two;

retain &names_retain;

length &names $5.;

set one;

run;

Thanks,
Suryakiran

View solution in original post


All Replies
Super User
Posts: 7,814

Re: How to change lengh of multiple variables in one go in SAS?

The length is set with a length statement. rename may help in avoiding certain NOTEs.

 

How do you determine the future lengths of the variables?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 9

Re: How to change lengh of multiple variables in one go in SAS?

Posted in reply to KurtBremser

Thanks for response Kurt. All the variables I have got have length of 3 & I need all of them to be changed to length of 5.

 

Just trying with the following method, but it's not working.

 

DATA DATASET1;

INPUT VAR1 $3. VAR2 $3. VAR3 $3. VAR4 $3. COL1 $3. COL2 $3. COL3 $3.;

DATALINES;

ABC BBB ASB AAS ATS EES DAS

ASD FDA ASD DAS AEW KJL ASD

AES EWL SAG GLA GKE AKS SAF

;

RUN;

 

DATA DATASET2;

SET DATASET1;

LENGTH VAR1-VAR4 $5. COL1-COL3 $5.;

RUN;

 

Super User
Posts: 7,814

Re: How to change lengh of multiple variables in one go in SAS?

Put the length statement before the set statement. With the set the data step compiler takes the lengths from the dataset and can't change them thereafter.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,970

Re: How to change lengh of multiple variables in one go in SAS?

Well, my question would be why not just:

data dataset1;
  input var1 $5. var2 $5. var3 $5. var4 $5. col1 $5. col2 $5. col3 $5.;
datalines;
ABC BBB ASB AAS ATS EES DAS
ASD FDA ASD DAS AEW KJL ASD
AES EWL SAG GLA GKE AKS SAF
;
run;

??

Why is it that data has incorrect lengths in the first place?  Fix the problem in the preceeding steps and save yourself the effort.  Also note that this change makes no difference char(3) of ABC still equals char(5) of ABC, so not seeing the value.

Frequent Contributor
Posts: 136

Re: How to change the length of multiple variables in one go in SAS?

As you said you have 70 variables, it is hard to type all the 70 variable names. I'm assuming that you have less variables in the dataset that not needed to change the variable length. Then this code works:

 

proc sql noprint;

select name into :names separated by " "

from dictionary.columns

where libname="SASHELP" /*Library */and

memname="CLASS"/*Dataset name*/ and name not in ("Height","Weight","Age") /* Variables to exclude for changing the length*/

;

quit;

%put &names;

data one;

length &names $10 ;

set sashelp.class;

run;

Thanks,
Suryakiran
Occasional Contributor
Posts: 9

Re: How to change the length of multiple variables in one go in SAS?

[ Edited ]
Posted in reply to SuryaKiran

Thank you guys for your thoughts. I guess the question was confusing so let me know reframe it correctly.

 

There are over 187 fields in a dataset in which I have to change the lengths for 78 variables from current length 3 to 5. The variable names goes like this. I can change it using the below method but would like to understand if there is any other efficient way to get it done. Typing all 78 variables like is bit tedious process.

 

AR1_ABC AR2_ADA AR3_ASD AR4_ASDF AR5_ASDEW AR6_SDFJ AR7_EWEK AR8_ASKE AR9_ASKWE AR10_KJREI AR11_KSKD AR12_KDFL AR13_LSI AR14_KSK AR15_SKE

 

DATA DATASET2;

LENGTH AR1_ABC $5. AR2_ADA $5. AR3_ASD $5. AR4_ASDF $5.AR5_ASDEW $5. AR6_SDFJ $5. AR7_EWEK $5. AR8_ASKE $5. AR9_ASKWE $5. AR10_KJREI $5.;

SET DATASET1;

RUN;

Super User
Super User
Posts: 7,970

Re: How to change the length of multiple variables in one go in SAS?

Well, you have really hit one of the reasons why most DB's, and a fair few standard data models work with normalised data rather than transposed data.  Personally, I would never have more than 20-30 variables in any one dataset.  Its just unweildy as you are finding.  Sure you could write some funky code to pull out metadata then generate code, or you could use arrays, at the end of the day you are going to have to do this on every process which bloats your coding effort at every level, and you lose most of the by group functionality.  You have also modelled your data in such a way as to not be able to use lists of variables (and set arrays easily), as you don't have the <variable><suffix number>

 

Now on top of the above data modelling discussion, the question still remains why the need to change the length at all. Is it because of processing later on?  If so then just include it there.  If thats how you import the data, change your import process to get lengths as you want.

 

Now in your code, you don't need to repeat the $5 each time:

LENGTH AR1_ABC AR2_ADA AR3_ASD ... AR10_KJREI $5;

Note, coding in upcase really makes code harder to read.  You will see in the above you only specify $5 once at the end.  If ar1_abc is positionally first in the dataset and ar10_kjrei is the last, and you want to change all between those positional variables, then you could simplify to:

LENGTH AR1_ABC--AR10_KJREI $5;

This means all variables between first specified and last specified.

 

But its all overshadowed by the simplicity of changing to a normalised structure.

Solution
‎12-14-2016 05:57 AM
Frequent Contributor
Posts: 136

Re: How to change the length of multiple variables in one go in SAS?

Using dictionary tables makes your task easier.
Instead of typing all the variable names it's easy to type the "VARNUM" numbers for the corresponding 
variables.

 

/* Here I have 10 variables and I need to change the length for only some of the variables */

data one;

infile datalines;

input AR1_ABC $3. AR2_ADA $3. AR3_ASD $3. AR4_ASDF $3.AR5_ASDEW $3. AR6_SDFJ $3. AR7_EWEK $3. AR8_ASKE $3. AR9_ASKWE $3. AR10_KJREI $3.;

datalines;

ABC BBB ASB AAS ATS EES DAS ASD FDA ASD

DAS AEW KJL ASD AES EWL SAG GLA GKE AKS

;

run;

/* Find the VARNUM numbers for the variables that you want to change the length from dictionary tables*/

PROC SQL;

select *

from dictionary.columns

where libname="WORK" and memname="ONE";

quit;

/* Put all the variable names that you want to change the length in a macro */

proc sql noprint;

select name into :names separated by " "

from dictionary.columns

where libname="WORK" and memname="ONE" and varnum in (1,5,6,9);

quit;

%put &names;

proc sql noprint;

select name into :names_retain separated by " "

from dictionary.columns

where libname="WORK" and memname="ONE" ;

quit;

%put &names_retain;

data two;

retain &names_retain;

length &names $5.;

set one;

run;

Thanks,
Suryakiran
Super User
Posts: 10,035

Re: How to change the length of multiple variables in one go in SAS?

Try SQL.

 

DATA DATASET1;
INPUT VAR1 $3. VAR2 $3. VAR3 $3. VAR4 $3. COL1 $3. COL2 $3. COL3 $3.;
DATALINES;
ABC BBB ASB AAS ATS EES DAS
ASD FDA ASD DAS AEW KJL ASD
AES EWL SAG GLA GKE AKS SAF
;
RUN;
data _null_;
 set sashelp.vcolumn(keep=libname memname type name
where=(libname='WORK' and memname='DATASET1' and type='char')) end=last;
 if _n_=1 then call execute('proc sql;alter table dataset1 modify');
 call execute(name||' char(5)');
 if last then call execute(';quit;');
  else call execute(',');
run;
Occasional Contributor
Posts: 9

Re: How to change the length of multiple variables in one go in SAS?

Hi All,

 

Yes, it's working from both the methods. Thanks a lot for your contributions & help on this. I really appreciate it. Thanks

Occasional Contributor
Posts: 9

Re: How to change the length of multiple variables in one go in SAS?

Ksharp, thanks for very much for the solution.

 

Can we apply the format and informat in the method you have mentioned?

 

DATA DATASET1;

INPUT VAR1 VAR2 VAR3 VAR4 COL1 COL2 COL3 $3.;

DATALINES;

ABC BBB ASB AAS ATS EES DAS

ASD FDA ASD DAS AEW KJL ASD

AES EWL SAG GLA GKE AKS SAF

;

RUN;

data _null_;

set sashelp.vcolumn(keep=libname memname type name

where=(libname='WORK' and memname='DATASET1' and type='char')) end=last;

if _n_=1 then call execute('proc sql;alter table dataset1 modify');

call execute(name||' char(5)');

if last then call execute(';quit;');

else call execute(',');

run;

Super User
Posts: 10,035

Re: How to change the length of multiple variables in one go in SAS?

Sure. The following cod make all the character variables have $20. format.


DATA DATASET1;
INPUT VAR1 $3. VAR2 $3. VAR3 $3. VAR4 $3. COL1 $3. COL2 $3. COL3 $3.;
DATALINES;
ABC BBB ASB AAS ATS EES DAS
ASD FDA ASD DAS AEW KJL ASD
AES EWL SAG GLA GKE AKS SAF
;
RUN;



data _null_;
 set sashelp.vcolumn(keep=libname memname type name
where=(libname='WORK' and memname='DATASET1' and type='char')) end=last;
 if _n_=1 then 
call execute('proc datasets library=work nolist nodetails;modify dataset1;format');
 call execute(name);
 if last then call execute(' $20.;quit;');
run;


Occasional Contributor
Posts: 9

Re: How to change the length of multiple variables in one go in SAS?

Great. It works, thank you very much Ksharp..I appreciate it.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1245 views
  • 6 likes
  • 5 in conversation