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

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

Vishy — Tue, 13 Dec 2016 14:54:17 GMT

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?

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

Kurt_Bremser — Tue, 13 Dec 2016 14:48:19 GMT

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?

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

Vishy — Tue, 13 Dec 2016 14:57:40 GMT

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;

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

Kurt_Bremser — Tue, 13 Dec 2016 15:00:33 GMT

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.

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

RW9 — Tue, 13 Dec 2016 15:15:52 GMT

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.

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

SuryaKiran — Tue, 13 Dec 2016 15:21:49 GMT

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;

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

Vishy — Tue, 13 Dec 2016 17:02:05 GMT

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;

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

RW9 — Tue, 13 Dec 2016 17:08:44 GMT

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.

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

SuryaKiran — Tue, 13 Dec 2016 20:02:34 GMT

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;

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

Ksharp — Wed, 14 Dec 2016 03:53:02 GMT

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;

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

Vishy — Wed, 14 Dec 2016 10:58:37 GMT

Hi All,

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

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

Vishy — Wed, 14 Dec 2016 16:34:24 GMT

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;

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

Ksharp — Thu, 15 Dec 2016 07:06:57 GMT

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;

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

Vishy — Thu, 15 Dec 2016 09:58:25 GMT

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