Desktop productivity for business analysts and programmers

Can you use parameters in a query?

Reply
Not applicable
Posts: 0

Can you use parameters in a query?

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!
SAS Super FREQ
Posts: 9,431

Re: Can you use parameters in a query?

Posted in reply to deleted_user
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
Not applicable
Posts: 0

Re: Can you use parameters in a query?

Posted in reply to Cynthia_sas
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!
Ask a Question
Discussion stats
  • 2 replies
  • 132 views
  • 0 likes
  • 2 in conversation