We have 12 large SAS-scripts that have minimal variations. They're basically just copy-pasted (only 4-5 lines are different). So if we optimize part of the code, we must do the same change in all 12 scripts.
I've stripped all the scripts down to the bare essentials, and placed the majority of the code into a new reusable script. So when the 12 SAS-scripts run, they %include the new reusable script. So far so good.
The problem is that 3 of the 12 SAS-scripts require a few unique columns to be renamed. This must be done roughly in the middle of the new reusable script file. My plan is for the reusable script file to %include a separate script that's used exclusively for renaming. But for now, I just want to make it work within the reusable script itself.
Each of the 12 scripts have a macro variable that indicates the script name. It's called &program and has values such as 1A, 1B, 1C, 2A, 2B, etc. The 3 scripts that require columns to be renamed are 2A, 2B, and 2C.
I need the code to work something like this (edit: unfortunately, some code becomes poorly structured once I save):
%macro rename;
%if &program = 2A %then %do;
rename COLUMN_NAME_1 = COLUMN_NAME_2A;
%end;
%if &program = 2B %then %do;
rename COLUMN_NAME_1 = COLUMN_NAME_2B;
%end;
%if &program = 2C %then %do;
rename COLUMN_NAME_1 = COLUMN_NAME_2C;
%end;
%mend rename;
%rename;
Or maybe something like this:
if &program = 2A then do; rename COLUMN_NAME_1 = COLUMN_NAME_2A; end;
if &program = 2B then do; rename COLUMN_NAME_1 = COLUMN_NAME_2B; end;
if &program = 2C then do; rename COLUMN_NAME_1 = COLUMN_NAME_2C; end;
So when the reusable script runs, and &prog = 2B, then the line rename COLUMN_NAME_1 = COLUMN_NAME_2B; should be "applied" in the reusable script.
I just can't make it work though. I've tried so many approaches, and never seem to get it just right. Sometimes it just doesn't register, other times I get obscure errors. Ultimately it's just about making if/then work with the &program macro variable within a data step.
What would be your recommended approach to solving this problem? I basically just want differential treatment of a few rows within an otherwise huge reusable code.
Hello @EinarRoed,
@EinarRoed wrote:
%macro rename;
%if &program = 2A %then %do; rename COLUMN_NAME_1 = COLUMN_NAME_2A; %end; %if &program = 2B %then %do; rename COLUMN_NAME_1 = COLUMN_NAME_2B; %end; %if &program = 2C %then %do; rename COLUMN_NAME_1 = COLUMN_NAME_2C; %end;
%mend rename;
%rename;Or maybe something like this:
if &program = 2A then do; rename COLUMN_NAME_1 = COLUMN_NAME_2A; end; if &program = 2B then do; rename COLUMN_NAME_1 = COLUMN_NAME_2B; end; if &program = 2C then do; rename COLUMN_NAME_1 = COLUMN_NAME_2C; end;
The first approach is syntactically correct (but could be simplified, see below), whereas the second approach is not (because the RENAME statement is not executable and the IF condition uses strings without quotes).
Instead of a macro you could use %SYSFUNC(IFC(...)) to create the RENAME statement dynamically:
%sysfunc(ifc(%sysfunc(findw(2A 2B 2C,&program)), rename COLUMN_NAME_1 = COLUMN_NAME_&program,));
So, you have a macro that tries to do this, but then you say
I just can't make it work though.
and we just stare at you with blank faces. How can we help you? We don't know what you have tried, and we don't know why it doesn't work.
Can you start by sharing the relevant parts of your code that doesn't work? Can you show us the LOG of this part of the code when it doesn't work?
Hello @EinarRoed,
@EinarRoed wrote:
%macro rename;
%if &program = 2A %then %do; rename COLUMN_NAME_1 = COLUMN_NAME_2A; %end; %if &program = 2B %then %do; rename COLUMN_NAME_1 = COLUMN_NAME_2B; %end; %if &program = 2C %then %do; rename COLUMN_NAME_1 = COLUMN_NAME_2C; %end;
%mend rename;
%rename;Or maybe something like this:
if &program = 2A then do; rename COLUMN_NAME_1 = COLUMN_NAME_2A; end; if &program = 2B then do; rename COLUMN_NAME_1 = COLUMN_NAME_2B; end; if &program = 2C then do; rename COLUMN_NAME_1 = COLUMN_NAME_2C; end;
The first approach is syntactically correct (but could be simplified, see below), whereas the second approach is not (because the RENAME statement is not executable and the IF condition uses strings without quotes).
Instead of a macro you could use %SYSFUNC(IFC(...)) to create the RENAME statement dynamically:
%sysfunc(ifc(%sysfunc(findw(2A 2B 2C,&program)), rename COLUMN_NAME_1 = COLUMN_NAME_&program,));
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: