How to efficiently count the number per month from 1991 to 2015?

France — Wed, 22 Aug 2018 12:58:25 GMT

Dear all,

I employed the following codes to count the number of cited 'docdb_family_id' per company per year. I first create a table to locate the 'docdb_family_id' for each 'psn_name' per year and then count the count the number of citation.

PROC SQL;
CREATE TABLE Step1.base_of_citation AS
SELECT
DISTINCT
Docdb_family_id AS docdb_family_id_base, co.psn_name
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
JOIN sample.Publications AS pu ON ap.appln_id = pu.appln_id
JOIN sample.Citations AS ci ON pu.pat_publn_id = ci.pat_publn_id
WHERE applt_seq_nr > 0 /* to force the query to look only at persons who are applicants*/
AND granted = 1 /* there exists a publication of the grant */
AND earliest_filing_date >= '01Jan1990'd
AND earliest_filing_date <= '31Dec1990'd
AND ci.cited_pat_publn_id > 0 
GROUP BY psn_name
ORDER BY psn_name
;
QUIT;

PROC SQL;
CREATE TABLE Step1.No_of_citation AS
SELECT
t_base.psn_name,
COUNT(DISTINCT t_do.docdb_family_id) AS No_of_citation
FROM Step1.base_of_citation AS t_base
JOIN Sample.Applications AS t_ap1 ON t_ap1.docdb_family_id = t_base.docdb_family_id_base
/* to set the moving window */
JOIN Sample.Docdbfamiliescitations AS t_do ON t_do.cited_docdb_family_id=t_ base.docdb_family_id_base
JOIN Sample.Applications AS t_ap2 ON t_ap2.docdb_family_id=t_do.docdb_famil y_id
JOIN Sample.Publications AS t_pu ON t_pu.appln_id=t_ap2.appln_id
WHERE YEAR(t_ap1.earliest_publn_date) NE 9999
GROUP BY t_base.psn_name
ORDER BY t_base.psn_name
;
QUIT;

But now, I need to count the number of 'docdb_family_id' per month from 1991 to 2015.

Could you please give me some suggestion about how to efficiently count the number of it? Is there any method I can use to calculate them by typing codes only one time rather than around 280 times?

Thanks in advance.

Best regards,

France

Re: How to efficiently count the number per month from 1991 to 2015?

Kurt_Bremser — Wed, 22 Aug 2018 13:05:27 GMT

Create a variable that contains the month in format YYYYMM, and use that in a class (or by - sorting needed) statement in proc summary, or as a group by in SQL.

Re: How to efficiently count the number per month from 1991 to 2015?

Reeza — Wed, 22 Aug 2018 14:59:06 GMT

PROC FREQ with a format applied will summarize by year.

proc freq data=YOUR_DATA_NAME;
table earliest_filing_date / out=want;
format earliest_filing_date year4.;
run;


proc print data=want;run;

topic Re: How to efficiently count the number per month from 1991 to 2015? in SAS Programming

How to efficiently count the number per month from 1991 to 2015?

Re: How to efficiently count the number per month from 1991 to 2015?

Re: How to efficiently count the number per month from 1991 to 2015?