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;
