Using DO LOOP to import excel files

vannesslee — Thu, 25 May 2017 05:32:11 GMT

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;

%mend extract_playerbillin;

%extract_playerbillin;

Re: Using DO LOOP to import excel files

Kurt_Bremser — Thu, 25 May 2017 07:33:29 GMT

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.

Re: Using DO LOOP to import excel files

RW9 — Thu, 25 May 2017 08:09:12 GMT

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;

topic Using DO LOOP to import excel files in SAS Enterprise Guide

Using DO LOOP to import excel files

Re: Using DO LOOP to import excel files

Re: Using DO LOOP to import excel files