SAS Using a DO loop to read in multiple files but need to append the table in each iteration to prevent losing data

Question

When I run my macros to input data from multiple files into one SAS table, it is replacing each dataset that is being read in once the next iteration begins. Thus, the only data that I am left with in my final table is the last file that I read in with the loop. How can I append my output table at the end of each read in iteration to prevent losing data each time I move to the next file?

Current Code:

  %let type=40;

%let year=2015;

%let months =07;

%let days = 01 02 03 04 05  06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31;

%let filetype=.csv;



%macro import_files;


                    %do K=1 %to 31; /*day*/

                           /*Scans through Macro Variable Arrays to find current month, year and letter group*/

                           %let nmbr = %scan(&type,1);

                           %let yr=%scan(&year,1);

                           %let mnth=%scan(&months,1);

                           %let day=%scan(&days,&K);



                           /*Creates string variables for Export and import locations of files using macro loop variables*/

                           %let exportfile = "C:\Users\wwn\Documents\HistoricalRateTesting\Top&nmbr&yr&mnth&day&filetype";

                           %let src = "\\2119\Raw_Data\Transposed_Canvass\Top&nmbr&yr&mnth&day&filetype";


                           /*Imports All files in that Alpha, Year and Month Group*/

                           DATA work.RAW_DATA;

                                INFILE &src firstobs=3 LRECL=1000 DELIMITER=',' TRUNCOVER DSD;
/*                              informat arv_dt mmddyy10. shop_dttm DATETIME18. arv_tm time5. rtrn_tm time5.;*/
/*                              format shop_dttm DATETIME18. arv_tm time5. rtrn_tm time5.;*/

                                INPUT

                                     city_cd          : $CHAR3.

                                     rtrn_city_cd     : $CHAR3.

                                     shop_car_type_cd : $CHAR4.

                                     shop_rt_categ    :  $CHAR2.

                                     shop_rt_type     :  $CHAR1.

                                     lor              : ?? BEST2.

                                     arv_tm           : ?? TIME5.

                                     rtrn_tm          : ??TIME5.

                                     arv_dt           : ?? MMDDYY10.

                                     shop_dttm        : ANYDTDTM.

                                     Brand_ZE         : ?? BEST10.2

                                     Brand_ZI         : ?? BEST10.2

                                     Brand_ZL         : ?? BEST10.2

                                     Brand_ZD         : ?? BEST10.2

                                     Brand_AL         : ?? BEST10.2

                                     Brand_ZR         : ?? BEST10.2

                                     Brand_ET         : ?? BEST10.2

                                     Brand_ZT         : ?? BEST10.2

                                     Brand_AD         : ?? BEST10.2

                                     Brand_ZA         : ?? BEST10.2

                                     Brand_EZ         : ?? BEST10.2

                                     Brand_SX         : ?? BEST10.2

                                     Brand_FX         : ?? BEST10.2

                                     Brand_FF         : ?? BEST10.2;

                RUN;

                                    %end;

                                     %mend import_files;
                                     %import_files;

You create every time a new dataset work.RAW_DATA this way. Just create it once for k=1 and then use proc append — kl78
Maybe this will be helpful. It looks like you've found an answer to your date question? communities.sas.com/t5/SAS-Communities-Library/… — Reeza

Eric Lecoutre Eric Lecoutre · Accepted Answer · 2016-06-18T09:14:51

As it seems all the files do have the same layout, you have a nice trick since SAS 8.2.

First declare the list of all files. In case your directory contains all the files you want to read -- and only those ones:

filename myfiles ("\\2119\Raw_Data\Transposed_Canvass\*");

Then use the declaration myfiles within the INFILE statement:

DATA work.RAW_DATA;  
    INFILE myfiles firstobs=3 LRECL=1000 DELIMITER=',' TRUNCOVER DSD;
/* ... */
RUN;

In case your folder contains other files, you would first have to precise which files you want to read with a small macro to expand list of files so that you have;

filename myfiles ("path_to_file1","path_to_file2","path_to_file3"...);

SAS Using a DO loop to read in multiple files but need to append the table in each iteration to prevent losing data

1 Answers