BookmarkSubscribeRSS Feed
France
Quartz | Level 8

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

 

2 REPLIES 2
Reeza
Super User

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;

 

 

 

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
  • 2 replies
  • 1547 views
  • 0 likes
  • 3 in conversation