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

Hi,

I have a dataset called TEMP and has column names as VAR1, VAR2, VAR3, ....., VAR300.

I would like to rename VAR1 to CARNUM, VAR2 to MARK, AND VAR3 ~ VAR300 in Y4-M2 format.

VAR3 = 2000-01, VAR4 = 2000-02, VAR5 = 2000-03, VAR6 = 2000-04, VAR7 = 2000-05, VAR8 = 2000-06, .... VAR15 = 2001-01, VAR16 = 2001-12, and ect.

Is there a way that I can use a do loop to rename VAR3 ~ VAR 300?

Many thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Tommywhosc
Obsidian | Level 7

You can have a macro write out all the PROC DATASETS....RENAME... text to an external file, then %include it....

data temp;   *create test data set ;
array v {*} var1-var300;
do x = 1 to 3;
do i = 1 to dim(v);
v{i}=ranuni(0);
end; output; end;
run;


/* Macro to write the variable name-pairs to an external file.     */
%macro r;
%let x = 2;  

%do yr4 = 2000 %to 2025;
%do mth = 1 %to 9; 
  %let x = %eval(&x+1);
  %let yrmo = _&yr4._0&mth;
  %let var = VAR&x;
  %if %eval(&x <= 300) %then %do;
  put @1 "  &var   =   &yrmo ";  %end;
%end;
%do mth = 10 %to 12;
  %let x = %eval(&x+1);
  %let yrmo = _&yr4._&mth;
  %let var = VAR&x;
  %if %eval(&x <= 300) %then %do;
  put @1 "  &var   =   &yrmo" ; %end;
%end;
%end;
%mend r;


data _null_;   *put PROC DATASETS..w/RENAME statement to external file ;
set temp;
file "C:\junk\rename.txt";
if _n_ = 1 then   
put @1 "Proc DATASETS lib=work;" /
  @1 "Modify TEMP; " /
  @1 "RENAME  "   ;

%r

put @1 " ;  quit; ";
stop;
run;

%include "C:\junk\rename.txt"

NOTE: I used standard SAS variable names - added some underscores. (See ballardW's comments).

View solution in original post

6 REPLIES 6
Steelers_In_DC
Barite | Level 11

Here's a template I've been using for a while, you'll have to play around with the variable names that you want changed.

proc sql noprint;

select cats(name,'=','OLD_',name)

into :prefixlist

separated by ' '

from dictionary.columns

where libname = 'MDJ' and lowcase(memname) = 'old_lgd_s1_base';

data mdj.old_lgd_s1_base(rename=(&prefixlist));

set mdj.old_lgd_s1_base;

run;

Ksharp
Super User

Make a map table Like:

VAR1   CARNUM

VAR2   MARK

VAR3   2000-01

Then call execute + proc datasets

ballardw
Super User

If you actually want the variable name to start with a digit and have a - (dash) not at underscore you will have to use the '2002-01'n form to use non-standard variable names.

Tommywhosc
Obsidian | Level 7

You can have a macro write out all the PROC DATASETS....RENAME... text to an external file, then %include it....

data temp;   *create test data set ;
array v {*} var1-var300;
do x = 1 to 3;
do i = 1 to dim(v);
v{i}=ranuni(0);
end; output; end;
run;


/* Macro to write the variable name-pairs to an external file.     */
%macro r;
%let x = 2;  

%do yr4 = 2000 %to 2025;
%do mth = 1 %to 9; 
  %let x = %eval(&x+1);
  %let yrmo = _&yr4._0&mth;
  %let var = VAR&x;
  %if %eval(&x <= 300) %then %do;
  put @1 "  &var   =   &yrmo ";  %end;
%end;
%do mth = 10 %to 12;
  %let x = %eval(&x+1);
  %let yrmo = _&yr4._&mth;
  %let var = VAR&x;
  %if %eval(&x <= 300) %then %do;
  put @1 "  &var   =   &yrmo" ; %end;
%end;
%end;
%mend r;


data _null_;   *put PROC DATASETS..w/RENAME statement to external file ;
set temp;
file "C:\junk\rename.txt";
if _n_ = 1 then   
put @1 "Proc DATASETS lib=work;" /
  @1 "Modify TEMP; " /
  @1 "RENAME  "   ;

%r

put @1 " ;  quit; ";
stop;
run;

%include "C:\junk\rename.txt"

NOTE: I used standard SAS variable names - added some underscores. (See ballardW's comments).

dcruik
Lapis Lazuli | Level 10

I haven't tested it yet, but after the data step to rename the first two variables, you could try something like the following rename macro.  Hope this helps!

data old;

set temp (rename=(VAR1=CARNUM VAR2=MARK));

run;

%macro rename(n);

%let year=2000;

%let month=01;

%do i=3 %to &n;

data new;

set old (rename=(VAR&i="&year-&month"n));

run;

%if &month<09 %then %do;

     %let month=0%eval(&month+1); %end;

%else %if &month<12 %then %do;

     %let month=%eval(&month+1); %end;

%else %do;

     %let month=01;

     %let year=%eval(&year+1); %end;

%end;

%mend;

%rename(300)

Reeza
Super User

Step1 - create mapping table

Step 2 - create rename statement

Step 3 - rename

There's a worked example in this question:

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
  • 6 replies
  • 2668 views
  • 8 likes
  • 7 in conversation