Hello,
I have some old data tables and each contain roughly the same variables (let's call them A, B, C, D, E, etc) and the names of these old data tables correspond with year they represent (let's call them 2016, 2017, and 2018). I want have a macro that will create new tables with variables that are conditionally selected from the old data tables instead of having to create each new table individually. I want to conditionally select variable C when the year is 2018 on top of also selecting variables A and B because the 2016 and 2017 data tables have variables A and B while the 2018 data table has A, B, and C. Here is my attempt at this:
%macro tables;
%do year=2016 %to 2018;
proc sql;
create table work.new&year as
select A, B
%if &year=2018 %then
%do;
C,
%end;
from library.old&year;
quit;
%end;
%mend tables;
%tables;
What happens when I execute my code is that the three new tables that I wanted were created but all three only contain the variables A and B. I have about 40 years of data tables that I need do this for and the number variables that I need to select grows from ~dozen to ~50 as I go from year 1 to year 40. Is this the right way to go about this problem? Any suggestions on how to get the new table for 2018 to have all three variables? Or, for this example, should I use macros in this manner (which works, but it is still more code than the previous macro):
%macro Var1;
A, B
%mend Var1;
%macro Var2;
%Var1, C
%mend Var2;
proc sql;
create table work.new2016 as
select %Var1 from library.old2016;
create table work.new2017 as
select %Var1 from library.old2017;
create table work.new2018 as
select %Var2 from library.old2018;
quit;
Any help would be much appreciated, thank you!
It seems to me that this code would fail to generate a data set for 2018, it would produce an error. You are missing a comma, and so as it was written you do not have valid SAS code being generated. This ought to work, if it does not, then show us the SASLOG with the options mprint; turned on before you run the program.
%macro tables;
%do year=2016 %to 2018;
proc sql;
create table work.new&year as
select A, B
%if &year=2018 %then ,C;
from library.old&year;
quit;
%end;
%mend tables;
%tables
It seems to me that this code would fail to generate a data set for 2018, it would produce an error. You are missing a comma, and so as it was written you do not have valid SAS code being generated. This ought to work, if it does not, then show us the SASLOG with the options mprint; turned on before you run the program.
%macro tables;
%do year=2016 %to 2018;
proc sql;
create table work.new&year as
select A, B
%if &year=2018 %then ,C;
from library.old&year;
quit;
%end;
%mend tables;
%tables
That worked perfectly, thank you very much!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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: