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

[France]

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.

[andreas_lds]

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=p0y8r... explains what merging does.

[France]

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.

[Astounding · Accepted solution]

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.