simplify the codes

[France]

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;
 
 

[Reeza · Accepted solution]

How big is each data set? Can you precombine them in a view and avoid the macro loop? That would be the fastest way to speed this up. And I notice multiple libraries. Are they all on the same DB or machine or are some on the server and some local on your machine or a different server?

[France]

Dear Reeza,

thanks for your help. I will precomine them and then count the number of data.