How to Import multiple Excel File/csv to SAS University Edition using sas macros

vjjags — Fri, 16 Mar 2018 23:39:43 GMT

I am using SAS University edition .

I have placed the 2 CSV files with different name in the same "my folder" and I want it to be imported to sas. I am trying to create macros for importing multiple csv or xls files .

my location of the file is "/folders/myshortcuts/myfolder/DS1"

Please suggest as to which part of the code is wrong ?

I have tried the below code but I am getting the error .

Code

%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;
run;
%end;
%end;

%end;
%end;
%else %put &dir cannot be open.;
%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive(/folders/myshortcuts/myfolder/DS1,csv)

Error in LOG

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 %macro drive(dir,ext);

74 %local cnt filrf rc did memcnt name;

75 %let cnt=0;

77 %let filrf=mydir;

78 %let rc=%sysfunc(filename(filrf,&dir));

79 %let did=%sysfunc(dopen(&filrf));

80 %if &did ne 0 %then %do;

81 %let memcnt=%sysfunc(dnum(&did));

83 %do i=1 %to &memcnt;

85 %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

87 %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

88 %if %superq(ext) = %superq(name) %then %do;

89 %let cnt=%eval(&cnt+1);

90 %put %qsysfunc(dread(&did,&i));

91 proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt

92 dbms=csv replace;

93 run;

94 %end;

95 %end;

97 %end;

98 %end;

99 %else %put &dir cannot be open.;

100 %let rc=%sysfunc(dclose(&did));

101

102 %mend drive;

103

104 %drive(/folders/myshortcuts/myfolder/DS1,csv)

H1b mod.csv

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

ERROR: Physical file does not exist, /folders/myshortcuts/myfolder/DS1\H1b mod.csv.

ERROR: Import unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.89 seconds

cpu time 0.23 seconds

h1b_kaggle.csv

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

ERROR: Physical file does not exist, /folders/myshortcuts/myfolder/DS1\h1b_kaggle.csv.

ERROR: Import unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.07 seconds

cpu time 0.06 seconds

105

106 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

119

Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros

SuzanneDorinski — Sat, 17 Mar 2018 00:00:14 GMT

Change the PROC IMPORT line to use &dir/%qsysfunc, instead of &dir\%qsysfunc.

proc import datafile="&dir/%qsysfunc(dread(&did,&i))" out=dsn&cnt

Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros

vjjags — Sat, 17 Mar 2018 00:07:37 GMT

Thanks Suzanne for your immediate reply now it works perfectly ..

Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros

vjjags — Sat, 17 Mar 2018 00:08:33 GMT

thanks for your prompt reply it works perfectly

topic Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros in SAS Programming

How to Import multiple Excel File/csv to SAS University Edition using sas macros

Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros

Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros

Re: How to Import multiple Excel File/csv to SAS University Edition using sas macros