Can't create a dataset with macro variable name

Reply
Frequent Contributor
Posts: 110

Can't create a dataset with macro variable name


Hi Sas experts
Am creating a macro using sashelp.prdsal2 dataset, but am unable to create a dataset with macro variable name. Please help me

Below is my code;

 


%macro pro(yr,pd);
proc sql;
create table test_"&pd." as
select state,PRODUCT,sum(Actual) as total from sashelp.prdsal2
where year=&yr. and product="&pd."
group by state,PRODUCT;
quit;
%mend;

%pro(yr=1995,pd=SOFA);

Esteemed Advisor
Posts: 6,646

Re: Can't create a dataset with macro variable name

Suppose that the macro variable pd contains the letters XX.

Then this

create table test_"&pd." as

will be expanded by the macro processor to

create table test_"XX" as

and you can clearly see that this will not work, as quotes are not allowed in dataset names

Omit the quotes and try again. If that still causes problems, use %put "&pd"; to display the contents of &pd and then post the log.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: Can't create a dataset with macro variable name

Hi 

I did as you suggested removed the quotations but still it is giving errors.  Please help

Below is the log.

 

%pro(yr=1995,pd=SOFA);
MLOGIC(PRO): Beginning execution.
MLOGIC(PRO): Parameter YR has value 1995
MLOGIC(PRO): Parameter PD has value SOFA
MPRINT(PRO): proc sql;
SYMBOLGEN: Macro variable PD resolves to SOFA
SYMBOLGEN: Macro variable YR resolves to 1995
SYMBOLGEN: Macro variable PD resolves to SOFA
MLOGIC(PRO): %PUT &pd quit
SYMBOLGEN: Macro variable PD resolves to SOFA
SOFA quit
MPRINT(PRO): create table test_SOFA as select state,PRODUCT,sum(Actual) as total from
sashelp.prdsal2 where year=1995 and product=SOFA group by state,PRODUCT
MLOGIC(PRO): Ending execution.
ERROR: The following columns were not found in the contributing tables: SOFA.
1604
1605 %put &pd;
WARNING: Apparent symbolic reference PD not resolved.

Grand Advisor
Posts: 17,325

Re: Can't create a dataset with macro variable name

Product=sofa tells SAS to compare against a column SOFA

Remember it meds to be valid SAS syntax, so in this case you need your quotation marks. 

Frequent Contributor
Posts: 110

Re: Can't create a dataset with macro variable name

Hi Reeza,

Actually am new to sas and its a bit confusing where should I mention qoutations.

Please write the right code.. if it is possible

Esteemed Advisor
Posts: 6,646

Re: Can't create a dataset with macro variable name

[ Edited ]

What you want should look like that:

%macro pro(yr,pd);
proc sql;
create table test_&pd. as
select state,PRODUCT,sum(Actual) as total from sashelp.prdsal2
where year=&yr. and product="&pd."
group by state,PRODUCT;
quit;
%mend;

%pro(yr=1995,pd=SOFA);

In short: do not use quotes when the contents of the macro variable are used as a SAS name or part of a SAS name, or as a numeric literal; use quotes when the contents of the macro variable are to be used as a string literal.

 

Remember that my first post was only about the create table ..... part.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: Can't create a dataset with macro variable name

Hi KurtBremser,

 

Its working

 

Thank you.

Ask a Question
Discussion stats
  • 6 replies
  • 308 views
  • 0 likes
  • 3 in conversation