topic Re: rename all variables based on one column value in SAS Programming

rename all variables based on one column value

aw016 — Tue, 01 Nov 2022 21:19:21 GMT

Hello,

I am trying to rename variables altogether based on the "session" value.

If session=1 then all variables should be var_bl; if session=2 then all variables should be var_12m.

I was wondering how to use concatenate function to do that. Previously I read some similar posts, but couldn't quite understand the algorithm.

Very much appreciate any explanation!!

Re: rename all variables based on one column value

Kurt_Bremser — Tue, 01 Nov 2022 21:35:13 GMT

Your question makes no sense. All variables in a dataset must have different names, so you cannot rename them to the same name.

If you have trouble describing your task, post examples for "before" and "after".

Re: rename all variables based on one column value

aw016 — Tue, 01 Nov 2022 21:41:50 GMT

Hi, what I meant is not to rename everything into the same name, but to add a suffix to all variables as "_bl" or "_12m" based on session value.

Re: rename all variables based on one column value

Kurt_Bremser — Tue, 01 Nov 2022 21:52:21 GMT

You retrieve the existing names from DICTIONARY.COLUMNS in PROC SQL.

With SELECT INTO you can build a macro variable which holds the rename pairs, for later use in PROC DATASETS.

%if &session = 1
%then %do;
%let suff = _bl;
%end;
%else %do;
%let suff = _12m;
%end;

proc sql noprint;
select catx("=",name,cats(name,"&suff.")) into :renames separated by " "
from dictionary.columns
where libname = "LIBRARY" and memname = "DATASET";
quit;

proc datasets lib=library;
modify dataset;
rename &renames.;
quit;

Re: rename all variables based on one column value

aw016 — Wed, 02 Nov 2022 01:21:43 GMT

Hi, thank you for your codes!

However, variables were just renamed to "_12m" instead of selected based on the session. I was wondering what would be the issue.

here is the code:

data one;
   input id  age session score1 score2 score3;
   datalines;
1 10 1 85 90 89
1  10 2 95 100 99
2  11 1 99 98 91
2   11 2 99 98 91
3  12 1 100 100 100
3  12 2 60 70 80
4   11 1 78 89 100
4 11 2 88 99 120
;
run;

%if &session = 1
%then %do;
%let suff = _bl;
%end;
%else %if &session=2 %do;
%let suff = _12m;
%end;

proc sql noprint;
select catx("=",name,cats(name,"&suff.")) into :renames separated by " "
from dictionary.columns
where libname = "WORK" and memname = "ONE";
quit;

proc datasets lib=work;
modify one;
rename &renames.;
quit;

Here is the log:

763 %if &session = 1
WARNING: Apparent symbolic reference SESSION not resolved.
764 %then %do;
WARNING: Apparent symbolic reference SESSION not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &session = 1
ERROR: Skipping to next %END statement.
765 %let suff = _bl;
766 %end;
767 %else %if &session=2 %do;
ERROR: Nesting of %IF statements in open code is not supported. %IF ignored.
ERROR: Skipping to next %END statement.
768 %let suff = _12m;
769 %end;
770

Re: rename all variables based on one column value

Kurt_Bremser — Wed, 02 Nov 2022 05:28:24 GMT

You cannot rename conditionally (on the value of a dataset variable). Your question therefore makes even less sense than before.

Please show the intended result for your example data.