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
... View more