Thank you, your suggestion works for me here is the combined code I used. /*this code download all the csv or excel files in the same directory, they don’t need to have the same file name or patterns, but they are output as dsn1 dsn2 …*/ %macro drive(dir,ext); %local cnt filrf rc did memcnt name; %let cnt=0; %let filrf=mydir; %let rc=%sysfunc(filename(filrf,&dir)); %let did=%sysfunc(dopen(&filrf)); %if &did ne 0 %then %do; %let memcnt=%sysfunc(dnum(&did)); %do i=1 %to &memcnt; %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.); %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do; %if %superq(ext) = %superq(name) %then %do; %let cnt=%eval(&cnt+1); %put %qsysfunc(dread(&did,&i)); proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt dbms=csv replace; guessingrows=max; GETNAMES=NO;/*whether excel or csv has the header or not, put no is easier to combine then change variable's name later*/ run; data dsn&cnt; length /*maximum length of the file name*/ Ticker $5; set dsn&cnt; Ticker="%qscan(%qsysfunc(dread(&did,&i)),1,.)"; run; %end; %end; %end; %end; %else %put &dir cannot be opened.; %let rc=%sysfunc(dclose(&did)); %mend drive; /*change your folder path below where the files are located, keep ,csv at the end though*/ %drive(C:\Users\Test, csv) /*combine all the files*/ data bigFile; set dsn:; run;
... View more
