BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I know you parameters can be used in the WHERE and GROUP portions of a query, but am wondering:

Can parameters be used in the "CREATE TABLE..." part of a query, like in the following piece of code:

CREATE TABLE HFA987A AS SELECT
EMPLOYEE.CAMPUS FORMAT=$63.,
&requestingcollege AS REQUESTING_COLLEGE format=$4.,
EMPLOYEE.CAMPUS_DESC FORMAT=$255.,
EMPLOYEE.COLLEGE FORMAT=$63.,

What I am trying to do is to incorporate my parameter data into my table being created. If anyone has an idea or two on how to do this, I would really appreciate hearing from you. Thanks in advance!
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
Hi... I would test it out this way:
[pre]
%let myparm = ABCD
%let mynum = 15;
proc sql;
create table new as
select name,
"&myparm" as requesting_college format=$4.,
&mynum as anumber,
age, height
from sashelp.class;
quit;

proc print data=new;
title 'should have 2 new columns, one char and one numeric';
run;
[/pre]

The bottom line is that you have to understand how macro variables work "outside" of being a parameter and then once you are dealing with parameters, you don't have any issues.

The difference in my code is that &myparm and &mynum are both assigned in the %let statement without any quotes -- this gives me the most flexibility to use them in subsequent code.

In "vanilla" PROC SQL code, IF I want to assign the constant value "ABCD" to the requesting_college variable and the number 10 to the anumber variable, then the code would be:
[pre]
proc sql;
create table new as
select name,
"ABCD" as requesting_college format=$4.,
10 as anumber,
age, height
from sashelp.class;
quit;
[/pre]

The quotes belong to the assignment of the character constant ABCD, so I need to have quotes around the &myparm reference (as shown in the first code example). But, the assignment for the anumber variable does NOT need quotes because I am creating a numeric constant for each row, so &mynum reference does NOT have quotes in the query code.

Essentially the SAS Macro facility is only generating code. So when the reference "&myparm" is encountered, the quotes belong to the syntax of the select statement and &myparm is passed to the macro processor to be resolved. If you put the reference to &myparm in single quotes, you will prevent the macro processor from resolving the macro reference. So you should always use double quotes where you need them.

fun macro tricks:
[pre]
%macro hello;
%global parm;
%put hello &parm;
title "Hello &parm";
title2 'Hello &parm';
proc print data=sashelp.class;
run;
%mend;

%let parm=world;
%hello;

%let parm=boys and girls, it is Howdy Doody time.;
%hello;
[/pre]

A neat debugging trick is to use the %put to write macro variables out to the SAS log. If you run this little snippet of code in a code node window, you should see that the macro variable reference in the single quotes was NOT resolved, but the macro variable reference in double quotes was resolved.

cynthia
deleted_user
Not applicable
Makes sense to me! Thank you so much for such a detailed explanation. I'll put the knowledge gained to good use.

Thank you so much!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 961 views
  • 0 likes
  • 2 in conversation