topic proc sql using macros in SAS Programming

proc sql using macros

Aspa — Mon, 13 Jun 2011 15:44:59 GMT

Hi All,

I have 30 different datasets in SAS and I would like to merge each one of them with a standard one I have. I am trying to write a macro to do that automatically for me. Unfortunately my macro (see below) does not work and I am not sure why this is the case. I get an error message:

WARNING: Apparent symbolic reference DISEASE_CLINIC not resolved.
ERROR 22-322: Expecting a name.

ERROR 200-322: The symbol is not recognized and will be ignored.

%macro merge(disease=);
proc sql;
create table test.&disease_clinic as
select *
from test.clinical_diagnosis left join test.&disease
on clinical_diagnosis.medcode=&disease.medcode;
quit;
%mend merge;
%merge (disease=tia);

Any ideas would be greatly appreciated.

Thanks,
A

Re: proc sql using macros

ballardw — Mon, 13 Jun 2011 16:41:39 GMT

I think the problem is in the line:

create table test.&disease_clinic as

SAS is expecting a macro variable named Disease_clinic to have been defined.
If you are wanting to use the name of the variable concatenated with _clinic the syntax should be:
create table test.&disease._clinic as

SAS uses a period to indicate the end of the macro variable name when used in compounds such as this.

Re: proc sql using macros

DF — Tue, 14 Jun 2011 16:50:43 GMT

You'll have the same issues in your column references too. E.g.:

clinical_diagnosis.medcode=&disease.medcode;

If the macro variable &disease computes to "ALPHA" then this would be equivalent to:

clinical_diagnosis.medcode=ALPHAmedcode;

If you're using the macro variable to reference a table name, then simply add a second dot, so

clinical_diagnosis.medcode=&disease..medcode;

Would become

clinical_diagnosis.medcode=ALPHA.medcode;