Can you use parameters in a query?

deleted_user — Wed, 16 May 2007 15:49:53 GMT

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!

Re: Can you use parameters in a query?

Cynthia_sas — Wed, 16 May 2007 20:17:09 GMT

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

Re: Can you use parameters in a query?

deleted_user — Wed, 16 May 2007 20:38:10 GMT

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!

topic Re: Can you use parameters in a query? in SAS Enterprise Guide

Can you use parameters in a query?

Re: Can you use parameters in a query?

Re: Can you use parameters in a query?