count the number per month during 15 years

[France]

Dear all,

I would like to count the number of granted and non-granted 'DOCDB_FAMILY_ID' per 'psn_name' per month from 1990 to 2015. At the moment, I only type the code to count the number per month in 1990. I guess I need to use macro function, but I really have no idea about how to use it. Could you please give some suggestion to run them efficiently?

PROC SQL;
create table step1.number_of_application as
select
co.psn_name,
count(distinct(case when earliest_filing_year = 1990 and month=1 then DOCDB_FAMILY_ID end)) as application_199001,
count(distinct(case when earliest_filing_year = 1990 and month=1 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199001,
count(distinct(case when earliest_filing_year = 1990 and month=2 then DOCDB_FAMILY_ID end)) as application_199002,
count(distinct(case when earliest_filing_year = 1990 and month=2 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199002,
count(distinct(case when earliest_filing_year = 1990 and month=3 then DOCDB_FAMILY_ID end)) as application_199003,
count(distinct(case when earliest_filing_year = 1990 and month=3 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199003,
count(distinct(case when earliest_filing_year = 1990 and month=4 then DOCDB_FAMILY_ID end)) as application_199004,
count(distinct(case when earliest_filing_year = 1990 and month=4 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199004,
count(distinct(case when earliest_filing_year = 1990 and month=5 then DOCDB_FAMILY_ID end)) as application_199005,
count(distinct(case when earliest_filing_year = 1990 and month=5 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199005,
count(distinct(case when earliest_filing_year = 1990 and month=6 then DOCDB_FAMILY_ID end)) as application_199006,
count(distinct(case when earliest_filing_year = 1990 and month=6 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199006,
count(distinct(case when earliest_filing_year = 1990 and month=7 then DOCDB_FAMILY_ID end)) as application_199007,
count(distinct(case when earliest_filing_year = 1990 and month=7 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199007,
count(distinct(case when earliest_filing_year = 1990 and month=8 then DOCDB_FAMILY_ID end)) as application_199008,
count(distinct(case when earliest_filing_year = 1990 and month=8 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199008,
count(distinct(case when earliest_filing_year = 1990 and month=9 then DOCDB_FAMILY_ID end)) as application_199009,
count(distinct(case when earliest_filing_year = 1990 and month=9 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199009,
count(distinct(case when earliest_filing_year = 1990 and month=10 then DOCDB_FAMILY_ID end)) as application_199010,
count(distinct(case when earliest_filing_year = 1990 and month=10 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199010,
count(distinct(case when earliest_filing_year = 1990 and month=11 then DOCDB_FAMILY_ID end)) as application_199011,
count(distinct(case when earliest_filing_year = 1990 and month=11 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199011,
count(distinct(case when earliest_filing_year = 1990 and month=12 then DOCDB_FAMILY_ID end)) as application_199012,
count(distinct(case when earliest_filing_year = 1990 and month=12 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199012
from Sample.applications as ap
join Sample.personapplication as pe on ap.appln_id = pe.appln_id
join Sample.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;

many thanks in advance,

best regards

France

[Reeza]

What about the PROC FREQ suggested in a previous thread, did that work for you?

If so, can you figure out how this could be modified to fit that form?

---

@France wrote:

Dear all,

I would like to count the number of granted and non-granted 'DOCDB_FAMILY_ID' per 'psn_name' per month from 1990 to 2015. At the moment, I only type the code to count the number per month in 1990. I guess I need to use macro function, but I really have no idea about how to use it. Could you please give some suggestion to run them efficiently?

PROC SQL;
create table step1.number_of_application as
select
co.psn_name,
count(distinct(case when earliest_filing_year = 1990 and month=1 then DOCDB_FAMILY_ID end)) as application_199001,
count(distinct(case when earliest_filing_year = 1990 and month=1 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199001,
count(distinct(case when earliest_filing_year = 1990 and month=2 then DOCDB_FAMILY_ID end)) as application_199002,
count(distinct(case when earliest_filing_year = 1990 and month=2 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199002,
count(distinct(case when earliest_filing_year = 1990 and month=3 then DOCDB_FAMILY_ID end)) as application_199003,
count(distinct(case when earliest_filing_year = 1990 and month=3 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199003,
count(distinct(case when earliest_filing_year = 1990 and month=4 then DOCDB_FAMILY_ID end)) as application_199004,
count(distinct(case when earliest_filing_year = 1990 and month=4 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199004,
count(distinct(case when earliest_filing_year = 1990 and month=5 then DOCDB_FAMILY_ID end)) as application_199005,
count(distinct(case when earliest_filing_year = 1990 and month=5 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199005,
count(distinct(case when earliest_filing_year = 1990 and month=6 then DOCDB_FAMILY_ID end)) as application_199006,
count(distinct(case when earliest_filing_year = 1990 and month=6 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199006,
count(distinct(case when earliest_filing_year = 1990 and month=7 then DOCDB_FAMILY_ID end)) as application_199007,
count(distinct(case when earliest_filing_year = 1990 and month=7 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199007,
count(distinct(case when earliest_filing_year = 1990 and month=8 then DOCDB_FAMILY_ID end)) as application_199008,
count(distinct(case when earliest_filing_year = 1990 and month=8 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199008,
count(distinct(case when earliest_filing_year = 1990 and month=9 then DOCDB_FAMILY_ID end)) as application_199009,
count(distinct(case when earliest_filing_year = 1990 and month=9 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199009,
count(distinct(case when earliest_filing_year = 1990 and month=10 then DOCDB_FAMILY_ID end)) as application_199010,
count(distinct(case when earliest_filing_year = 1990 and month=10 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199010,
count(distinct(case when earliest_filing_year = 1990 and month=11 then DOCDB_FAMILY_ID end)) as application_199011,
count(distinct(case when earliest_filing_year = 1990 and month=11 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199011,
count(distinct(case when earliest_filing_year = 1990 and month=12 then DOCDB_FAMILY_ID end)) as application_199012,
count(distinct(case when earliest_filing_year = 1990 and month=12 and granted= 1 then DOCDB_FAMILY_ID end)) as granted_199012
from Sample.applications as ap
join Sample.personapplication as pe on ap.appln_id = pe.appln_id
join Sample.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;

many thanks in advance,

best regards

France

---