topic Re: Dynamically rename a bunch of vriables into new variables: Y1, Y2.... in New SAS User

Dynamically rename a bunch of vriables into new variables: Y1, Y2....

htn1 — Tue, 24 Sep 2019 22:47:55 GMT

Hi Experts,

I am trying to rename a bunch of old variables as Y1, Y2, Y3, …. but running into trouble. See code below. Any help would be greatly appreciated.

Thanks

Harrison:

%let varlist=
A0A075B6K4 A0A075B6K5 A2NJV5 O14562 O96009 P00390 P01742 P01834 P01892 P02655 P02751 P02765 P02786 P02787 P02790 P09467 P11686 P16152 P18065 P20039 P30405 P30838 P35247 P43652 P49006 P50238 P61326 P63151 P02768_1 P06681_1 P14678_1 P22897_1 P23246_1 P29279_1 P51159_1 P61201_1 Q02790 Q02818 Q03252 Q14956 Q16401 Q99758 Q7Z2W4 Q8IWL2_1 Q8N9N7 Q96AG4 Q96IU4 Q96PD5 Q96S96 Q9BPX5 Q9BQ61 Q9BUL8 Q9BWS9_1 Q9H1Z4 Q9NQ79 Q9P2T1_1 Q9UGT4 Q9Y3E1;

data _null_;
length old $32 rename $4;
do i=1 to countw("&varlist");
old=scan("&varlist",i);
rename &old = Y&i;

end;
call symputx('rename',rename);
run;

Re: Dynamically rename a bunch of vriables into new variables: Y1, Y2....

Tom — Tue, 24 Sep 2019 23:24:35 GMT

Not sure why you have that RENAME statement in the middle of the data step where you are attempting to generate the information that you will need to make an actual RENAME statement.

data _null_;
  length old $32 rename $32767;
  do i=1 to countw("&varlist");
    old=scan("&varlist",i);
    rename=catx(' ',rename,catx('=',&old,cats('Y',i)));
  end;
  call symputx('rename',rename);
run;

proc datasets nolist lib=mylib ;
  modify mydata ;
  rename &rename ;
  run;
quit;

Re: Dynamically rename a bunch of vriables into new variables: Y1, Y2....

Astounding — Tue, 24 Sep 2019 23:29:13 GMT

There's no mention here of any input or output data set. So if you can get the program to work, what would its results be? Would it be a one-time application or would it need to be used with different values for &varlist?

Re: Dynamically rename a bunch of vriables into new variables: Y1, Y2....

RichardDeVen — Wed, 25 Sep 2019 19:37:31 GMT

A space separated list of value can be parsed into items and an expression can be performed to transform each item. This process is known as map or apply in some programming circles.

Construct a map macro for parsing items from a list and resolving a passed in expression for each item. The macro provides 'automatic' macro variable item and _n_ for use in the expression.

Example:

Each item in varlist is mapped to <item>=Y<_n_> which can be used in a rename statement or option.

%macro map(items, expr);
  %local item _N_;
  %do _n_ = 1 %to %sysfunc(countw(&items));
    %let item = %scan(&items,&_n_);
    %unquote(&expr)
  %end;
%mend;

%let varlist = 
  A0A075B6K4 A0A075B6K5 A2NJV5 O14562 O96009 P00390 P01742 P01834 P01892 P02655 P02751 
  P02765 P02786 P02787 P02790 P09467 P11686 P16152 P18065 P20039 P30405 P30838 P35247
  P43652 P49006 P50238 P61326 P63151 P02768_1 P06681_1 P14678_1 P22897_1 P23246_1 
  P29279_1 P51159_1 P61201_1 Q02790 Q02818 Q03252 Q14956 Q16401 Q99758 Q7Z2W4 Q8IWL2_1 Q8N9N7 
  Q96AG4 Q96IU4 Q96PD5 Q96S96 Q9BPX5 Q9BQ61 Q9BUL8 Q9BWS9_1 Q9H1Z4 Q9NQ79 Q9P2T1_1 Q9UGT4 Q9Y3E1
  ;

data have;
  retain &varlist 1.;
run;

data want;
  set have;
  rename %map(&varlist,%nrstr(&item.=y&_N_.));
run;

The same idea, but mapping to <item> as Y<_n_> for use in a SQL select

proc sql;
  create table want2(drop=sentinel) as
  select 
    %map(&varlist,%nrstr(&item. as y&_N_.,))
    . as sentinel
  from have;