topic Proc SQL, macro variable in SAS Programming

Proc SQL, macro variable

perkinsb2 — Wed, 27 Dec 2017 16:45:27 GMT

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;

Re: Proc SQL, macro variable

PaigeMiller — Wed, 27 Dec 2017 16:59:20 GMT

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.

Re: Proc SQL, macro variable

Quentin — Wed, 27 Dec 2017 17:00:04 GMT

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.