how to merge data each month over the period from 2005 to 2015.

France — Thu, 06 Sep 2018 10:39:33 GMT

Dear all,

When I want to merge data from 200501 to 201512 by following codes,

macro  base_of_cit4;
   %local y;
   %do y=2005 %to 2015;

   %local m;
   %do m=1 %to 12;

DATA Step4.number_of_cit_end ;
   MERGE Step3.Base3_of_cit&y.&m - Step3.Base3_of_cit&y.&m ;
   BY psn_name;
RUN;
     %end; 
     %end;

%mend base_of_cit4;   

%base_of_cit4;

the result is

ERROR: File STEP3.BASE3_OF_CIT118382.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118383.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118384.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118385.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118386.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118387.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118388.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118389.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118390.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118391.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118392.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118393.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118394.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118395.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118396.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118397.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118398.DATA does not exist.
ERROR: File STEP3.BASE3_OF_CIT118399.DATA does not exist.
....

204  RUN;

205  %macro  base_of_cit4;
206     %local y;
207     %do y=2005 %to 2015;
208
209     %local m;
210     %do m=1 %to 12;
211
212  DATA Step5.number_of_cit_end ;
213     MERGE Step3.Base3_of_cit&y.&m - Step3.Base3_of_cit&y.&m ;
214     BY psn_name;
215  RUN;
216       %end;
217       %end;
218
219  %mend base_of_cit4;
220
221  %base_of_cit4;

NOTE: There were 4584 observations read from the data set STEP3.BASE3_OF_CIT20051.
NOTE: The data set STEP5.NUMBER_OF_CIT_END has 4584 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.86 seconds
      cpu time            0.03 seconds



NOTE: There were 4961 observations read from the data set STEP3.BASE3_OF_CIT20052.
NOTE: The data set STEP5.NUMBER_OF_CIT_END has 4961 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.28 seconds
      cpu time            0.04 seconds



NOTE: There were 5561 observations read from the data set STEP3.BASE3_OF_CIT20053.
NOTE: The data set STEP5.NUMBER_OF_CIT_END has 5561 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.21 seconds
      cpu time            0.04 seconds

....

NOTE: There were 0 observations read from the data set STEP3.BASE3_OF_CIT201511.
NOTE: The data set STEP4.NUMBER_OF_CIT2015 has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds



NOTE: There were 0 observations read from the data set STEP3.BASE3_OF_CIT201512.
NOTE: The data set STEP4.NUMBER_OF_CIT2015 has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

Finally, I only get an empty table, could you please give me some suggestion about this?

thanks in advance.

Re: how to merge data each month over the period from 2005 to 2015.

andreas_lds — Thu, 06 Sep 2018 10:59:04 GMT

Are you sure, that you need to merge the datasets?

EDIT: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=basess&docsetTarget=p0y8rrzaeklkkin1tlyxeq5qskkp.htm&locale=de explains what merging does.

Re: how to merge data each month over the period from 2005 to 2015.

France — Thu, 06 Sep 2018 11:45:18 GMT

hello,
Yes, I am sure, cause I create the table for the number_of_cit each month for different companies during the period between 2005 and 2015. So I have 132 tables, and each table has only two variables, one is the company name, the other is the number_of_cit at the month ( e.g., 200501, 201512). That is why I need to merge them together.

Re: how to merge data each month over the period from 2005 to 2015.

Astounding — Thu, 06 Sep 2018 12:16:54 GMT

If you simply want to merge ALL of the data sets with that name pattern, use:

data Step4.Number_of_cit_end;

merge Step3.Base3_of_cit20: ;

by ps_name;

run;

No macros, no looping.

That should handle your original question. However, I too question whether MERGE is the right thing to do.

topic how to merge data each month over the period from 2005 to 2015. in SAS Programming

how to merge data each month over the period from 2005 to 2015.

Re: how to merge data each month over the period from 2005 to 2015.

Re: how to merge data each month over the period from 2005 to 2015.

Re: how to merge data each month over the period from 2005 to 2015.