BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vishy
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

13 REPLIES 13
Vishy
Obsidian | Level 7

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SuryaKiran
Meteorite | Level 14

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
Vishy
Obsidian | Level 7

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SuryaKiran
Meteorite | Level 14

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
Ksharp
Super User

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;
Vishy
Obsidian | Level 7

Hi All,

 

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

Vishy
Obsidian | Level 7

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;

Ksharp
Super User
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;


Vishy
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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