Using DO LOOP to import excel files

[vannesslee]

Hi all,

I have been trying below codes to import excel files using do loop and there is no error shown in the log. However, there is no output after I have tried to run below script. Anyone can highlight any error/issues with the below script?

%let s_folder = \\10.64.78.49\Users\88094701\billsinstats;

%macro extract_playerbillin;

     data _null_;

           startdt = '1Apr2017'd;

           enddt = '30Apr2017'd;

         call symput('startdt', startdt);

         call symput('enddt', enddt);

     run;

%do dt=&startdt %to &enddt;

     data _null_;

           datadate = &dt;

           filedate = put(datadate, yymmddn8.);

         call symput('filedate', put(datadate, yymmddn8.));

           call symput('filepath',"&s_folder\igt_bill_in__&filedate..xlsx");

           put filedate ;

     run;

     %if %sysfunc(fileexist("&filepath")) %then %do;

           data _abs_player_billsin;

                attrib Date length=8 format=datetime21.2;

                attrib Machine length=$10 format=$char10.;

                attrib Location length=$10 format=$char10.;

                attrib EventCode length=$20 format=$char20.;

                attrib Event length=$50 format=$char50.;

                attrib ‘Bonus ID’n length=$10 format=$char10.;

                attrib Amount length=8 format=best12.;

                attrib Staff length=$50 format=$char50.;

                attrib ‘ID #’n length=8 format=best20.;

                attrib ‘Insert Date’n length=8 format=datetime21.2;

                infile "&filepath"

                           delimiter = ','

                           dsd

                           truncover

                           firstobs=2

                           lrecl=500;

                keep Date Machine Location;

           run;

           %if &dt=&startdt %then %do;

                data abs_player_billsin;

                     set _abs_player_billsin (obs=0);

                run;

           %end;

           proc append

                data = _abs_player_billsin

                base = abs_player_billsin;

           run;

           proc datasets lib=work nolist nowarn;

                delete     _abs_player_billsin;

           quit;

     %end;

%end;

%mend extract_playerbillin;

%extract_playerbillin;

[Kurt_Bremser]

You can't read a true xlsx file with a data step. XLSX files are zip-compressed XML.

Also use options mprint mlogic to show what the macro is really doing.

[RW9]

Datastep to create the loop, call execute to generate one proc import and either a dataset set or proc append for each value in the given range:

data _null_;
  do dt='01apr2017'd to '30apr2017'd;

    call execute('proc import datafile="'||cats(&filepath.,&filename.,put(dt,date9.),".xlsx")||'" out=tmp replace; run;');
    if _n_=1 then call execute('data abs_player_billsin; set tmp; run;');
    else call execute('proc append base=abs_player_billsin data=tmp; run;')

end; run;