BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
France
Quartz | Level 8

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;
 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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?

View solution in original post

2 REPLIES 2
Reeza
Super User
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
Quartz | Level 8

Dear Reeza,

 

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

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
  • 1063 views
  • 0 likes
  • 2 in conversation