I have a question on a Proc SQL code using a macro variable. The below code is selecting a macro variable as PRD_END_DT. I do not understand how the code can be selecting a macro variable, I have not seen syntax like this before. Any help is appreciated, to help me understand. Also, I do not understand why this is selecting 'ACH', how can this be referring to selecting a column in out.Represent_data_ACH_V3&Ext? I am trying to understand this code and could not find anything in search results similar to this. Thanks!
proc sql;
create table out.Represent_ACH_grouped&Ext as
select &EndPeriod. as PRD_END_DT,'ACH' as Channel,represent_group, count(*) as items
from out.Represent_data_ACH_V3&Ext
group by 3;
quit;
Macro variables resolve to text, i.e. SAS code.
If you submit:
%put &=EndPeriod;
%put &=Ext;
it will print the resolved values of those two variables to the log. What do you see in the log when you run that?
Let's suppose that you have macro variables like:
6 %let EndPeriod="27Dec2017"d;
7 %let Ext=Production;
8
9 %put &=EndPeriod;
ENDPERIOD="27Dec2017"d
10 %put &=Ext;
EXT=Production
Then when the macro variables resolve, your SQL code would be like:
proc sql;
create table out.Represent_ACH_groupedProduction as
select
"27Dec2017"d as PRD_END_DT
,'ACH' as Channel
,represent_group
,count(*) as items
from out.Represent_data_ACH_V3Production
group by 3
;
quit;
That is valid PROC SQL code (or at least looks like it, I didn't run it). On the select statement you can put literal values in addition to column names. So 'ACH' as Channel puts the character string ACH into the column Channel. "27Dec2017"d as PRD_END_DT puts the numeric value 21180 (which is the SAS date for Dec 27, 2017) into the column PRD_END_DT.
The macro variables must have a value assigned to them before you run PROC SQL. Then, all the macro processor does is replace the macro variable name with the value assigned to it when your run PROC SQL.
So, for example, &endperiod has to have a value prior to SQL, let's say it's value is VAR22, and so VAR22 is used where &endperiod appears in the code.
Macro variables resolve to text, i.e. SAS code.
If you submit:
%put &=EndPeriod;
%put &=Ext;
it will print the resolved values of those two variables to the log. What do you see in the log when you run that?
Let's suppose that you have macro variables like:
6 %let EndPeriod="27Dec2017"d;
7 %let Ext=Production;
8
9 %put &=EndPeriod;
ENDPERIOD="27Dec2017"d
10 %put &=Ext;
EXT=Production
Then when the macro variables resolve, your SQL code would be like:
proc sql;
create table out.Represent_ACH_groupedProduction as
select
"27Dec2017"d as PRD_END_DT
,'ACH' as Channel
,represent_group
,count(*) as items
from out.Represent_data_ACH_V3Production
group by 3
;
quit;
That is valid PROC SQL code (or at least looks like it, I didn't run it). On the select statement you can put literal values in addition to column names. So 'ACH' as Channel puts the character string ACH into the column Channel. "27Dec2017"d as PRD_END_DT puts the numeric value 21180 (which is the SAS date for Dec 27, 2017) into the column PRD_END_DT.
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 25. 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.