Using Macro with PROC SQL

France — Thu, 30 Aug 2018 13:50:27 GMT

Hello all,

I would like to count the number of DOCDB_FAMILY_ID per psn_name per month between 1990 and 2015 by following codes,

%macro each_month;
%local p;
%do p=1990 %to 2015;

PROC SQL;
create table Sa_step3.number_of_application&p as
select
co.psn_name,
count(distinct(case when month = 1 then DOCDB_FAMILY_ID end)) as application_&p01,
count(distinct(case when month = 1 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p01,
count(distinct(case when month = 2 then DOCDB_FAMILY_ID end)) as application_&p02,
count(distinct(case when month = 2 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p02,
count(distinct(case when month = 3 then DOCDB_FAMILY_ID end)) as application_&p03,
count(distinct(case when month = 3 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p03,
count(distinct(case when month = 4 then DOCDB_FAMILY_ID end)) as application_&p04,
count(distinct(case when month = 4 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p04,
count(distinct(case when month = 5 then DOCDB_FAMILY_ID end)) as application_&p05,
count(distinct(case when month = 5 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p05,
count(distinct(case when month = 6 then DOCDB_FAMILY_ID end)) as application_&p06,
count(distinct(case when month = 6 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p06,
count(distinct(case when month = 7 then DOCDB_FAMILY_ID end)) as application_&p07,
count(distinct(case when month = 7 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p07,
count(distinct(case when month = 8 then DOCDB_FAMILY_ID end)) as application_&p08,
count(distinct(case when month = 8 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p08,
count(distinct(case when month = 9 then DOCDB_FAMILY_ID end)) as application_&p09,
count(distinct(case when month = 9 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p09,
count(distinct(case when month = 10 then DOCDB_FAMILY_ID end)) as application_&p10,
count(distinct(case when month = 10 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p10,
count(distinct(case when month = 11 then DOCDB_FAMILY_ID end)) as application_&p11,
count(distinct(case when month = 11 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p11,
count(distinct(case when month = 12 then DOCDB_FAMILY_ID end)) as application_&p12,
count(distinct(case when month = 12 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p12
from Sa_step2.year&p as ap
join Pat_sam.Personapplication as pe on ap.appln_id = pe.appln_id
join Pat_sam.Companies as co on pe.person_id = co.person_id
where applt_seq_nr > 0
and ap.ipr_type = 'PI'
group by psn_name
order by psn_name
;
quit;

%end;
   run;
 
%mend each_month;
 
%each_month

but the result shows that

NOTE: Line generated by the invoked macro "EACH_MONTH".
351              create table Sa_step3.number_of_application&p as select co.psn_name,
351! count(distinct(case when month = 1 then DOCDB_FAMILY_ID end)) as application_&p01,
                                                                                  -
                                                                                  22
                                                                                  200
351! count(distinct(case when month = 1 and granted = 1 then DOCDB_FAMILY_ID end)) as
WARNING: Apparent symbolic reference P01 not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM,
              INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

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

NOTE: Line generated by the invoked macro "EACH_MONTH".
352   granted_&p01, count(distinct(case when month = 2 then DOCDB_FAMILY_ID end)) as application_&p02,
              -
              22
              76
352!  count(distinct(case when month = 2 and granted = 1 then DOCDB_FAMILY_ID end)) as granted_&p02,
352! count(distinct(case when month = 3 then DOCDB_FAMILY_ID end))
WARNING: Apparent symbolic reference P01 not resolved.
WARNING: Apparent symbolic reference P02 not resolved.
WARNING: Apparent symbolic reference P02 not resolved.
WARNING: Apparent symbolic reference P03 not resolved.
WARNING: Apparent symbolic reference P03 not resolved.
WARNING: Apparent symbolic reference P04 not resolved.
WARNING: Apparent symbolic reference P04 not resolved.
WARNING: Apparent symbolic reference P05 not resolved.
WARNING: Apparent symbolic reference P05 not resolved.
WARNING: Apparent symbolic reference P06 not resolved.
WARNING: Apparent symbolic reference P06 not resolved.
WARNING: Apparent symbolic reference P07 not resolved.
WARNING: Apparent symbolic reference P07 not resolved.
WARNING: Apparent symbolic reference P08 not resolved.
WARNING: Apparent symbolic reference P08 not resolved.
WARNING: Apparent symbolic reference P09 not resolved.
WARNING: Apparent symbolic reference P09 not resolved.
WARNING: Apparent symbolic reference P10 not resolved.
WARNING: Apparent symbolic reference P10 not resolved.
WARNING: Apparent symbolic reference P11 not resolved.
WARNING: Apparent symbolic reference P11 not resolved.
WARNING: Apparent symbolic reference P12 not resolved.
WARNING: Apparent symbolic reference P12 not resolved.
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL,
              GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT,
              UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

could you please give me any suggestion about this? beside, can i use macro variable like this ?

thanks in advance,

best regards,

France

Re: Using Macro with PROC SQL

RW9 — Thu, 30 Aug 2018 14:20:09 GMT

Its very hard to tell from a long line of code, and no test data/required output. Can you not just two sqls:

select count(docdb_family_id) as application_count
from ...
group by month;

select count(docdb_family_id) as granted_count
from ...
group by month
having granted=1;

There isn't any need to do each month individually.

Re: Using Macro with PROC SQL

Quentin — Thu, 30 Aug 2018 14:29:37 GMT

You coded:

application_&p01

That makes SAS look for a macro variable named P01, which doesn't exist. You have a macro variable named P. You can add a dot after the P, to tell the macro processor that that is the end of the macro variable name. So:

application_&p.01

That should fix the macro resolution error. SAS will resolve macro variable P, and the 01 is just text. But big picture, there may be better ways to do this, as @RW9suggested.

Re: Using Macro with PROC SQL

France — Thu, 30 Aug 2018 16:27:17 GMT

Dear RW9,

thanks for your advice.

have a nice day.
best regards.
France

topic Re: Using Macro with PROC SQL in SAS Programming

Using Macro with PROC SQL

Re: Using Macro with PROC SQL

Re: Using Macro with PROC SQL

Re: Using Macro with PROC SQL