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
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: