BookmarkSubscribeRSS Feed
France
Quartz | Level 8

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

 

1 REPLY 1
Reeza
Super User

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

 


 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 722 views
  • 0 likes
  • 2 in conversation