BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
perkinsb2
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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.

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Quentin
Super User

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.

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1750 views
  • 1 like
  • 3 in conversation