Dear all,
I am running following codes, which may be need to spend total 2.5 days. Could you please give me some suggestion to simplify the codes, thereby saving times?
%macro base_of_cit;
%local y;
%do y=2005 %to 2015;
%local m;
%do m=1 %to 12;
PROC SQL;
CREATE TABLE Step2.base_of_cit&y.&m AS
SELECT
DISTINCT
Docdb_family_id AS docdb_family_id_base,
co.psn_name
FROM
Step2.year&y AS ap
JOIN Pat_ori.Personapplication AS pe ON ap.appln_id = pe.appln_id
JOIN Pat_ori.Companies AS co ON pe.person_id = co.person_id
JOIN Pat_ori.Publicationsnew AS pu ON ap.appln_id = pu.appln_id
JOIN Pat_ori.Citations AS ci ON pu.pat_publn_id = ci.pat_publn_id
WHERE
pe.applt_seq_nr > 0 /* to force the query to look only at persons who are applicants*/
AND ap.month = &m
AND ci.cited_pat_publn_id > 0 /* to guarantee that all cited publications have been included in the sample */
GROUP BY psn_name
ORDER BY psn_name
;
QUIT;
PROC SQL;
CREATE TABLE Step3.No_of_cit&y.&m AS
SELECT
t_base.psn_name,
COUNT(DISTINCT t_do.docdb_family_id) AS No_of_citation
FROM
Step2.base_of_cit&y.&m AS t_base
JOIN Pat_ori.Applicationsnew AS t_ap1 ON t_ap1.docdb_family_id = t_base.docdb_family_id_base /* to set the moving window */
JOIN Pat_ori.Docdbfamiliescitations AS t_do ON t_do.cited_docdb_family_id=t_base.docdb_family_id_base
JOIN Pat_ori.Applicationsnew AS t_ap2 ON t_ap2.docdb_family_id=t_do.docdb_family_id
JOIN Pat_ori.Publicationsnew AS t_pu ON t_pu.appln_id=t_ap2.appln_id
WHERE
YEAR(t_ap1.earliest_publn_date) NE 9999
AND YEAR(t_pu.publn_date) NE 9999
AND t_pu.publn_date <= intnx('year',t_ap1.earliest_publn_date,6) /* a 6-year moving window starting at the earliest publication date of a patent family */
GROUP BY t_base.psn_name
ORDER BY t_base.psn_name
;
QUIT;
%end;
%end;
%mend base_of_cit;
%base_of_cit;
Dear Reeza,
thanks for your help. I will precomine them and then count the number of data.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.