topic Re: Import multiple excel file from a folder with dsetnames as excel names in SAS Procedures

Import multiple excel file from a folder with dsetnames as excel names

sas_Forum — Wed, 13 Jun 2012 07:49:12 GMT

Hi i am 100 excel files i want to create them as sastasets at once how can
i do it

The path is

:c:/dir

Excel names like:

allhabad & guj main.xls
hyd.xls
delhi main & dis.xls
...

like this how can i do by crating a macro

Re: Import multiple excel file from a folder with dsetnames as excel names

Ksharp — Wed, 13 Jun 2012 07:58:07 GMT

EXAMPLE:

%let subdir=D:\FTP\F-NASE09_18\;
filename dir pipe "dir &subdir.*.xls /B";
data _null_;
 infile dir truncover end=last;
 input filename  $100.;
 length f $ 8;
 f=filename;
 call symputx(cats('dsn',_n_),f);
 filename=cats("&subdir",filename);
 call symputx(cats('path',_n_),filename);
 if last then call symputx('nobs',_n_);
run;
%put _user_;

%macro import;
%do i=1 %to &nobs;
proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace;
                                              getnames=no;
                                              mixed=yes;
                                              run;
%end;
%mend import;

%import

Re: Import multiple excel file from a folder with dsetnames as excel names

sas_Forum — Wed, 13 Jun 2012 08:11:08 GMT

i have changed the here only %let subdir="my paht"

i am getting this error

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

NOTE: Line generated by the macro variable "DSN1".

103 /usr/bin

200

Re: Import multiple excel file from a folder with dsetnames as excel names

Ksharp — Wed, 13 Jun 2012 08:19:02 GMT

You are under UNIX environment.

%let subdir=/usr/bin ;

filename dir pipe "ls &subdir/*.xls ";

data _null_;

infile dir truncover end=last expandtabs ;

input filename : $100. @@;

f=scan(filename,1,'.') ;

call symputx(cats('dsn',_n_),f);

filename=cats("&subdir",filename);

call symputx(cats('path',_n_),filename);

if last then call symputx('nobs',_n_);

run;

%put _user_;

%macro import;

%do i=1 %to &nobs;

proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace;

getnames=no;

mixed=yes;

run;

%end;

%mend import;

%import

Ksharp

Re: Import multiple excel file from a folder with dsetnames as excel names

sas_Forum — Wed, 13 Jun 2012 10:28:15 GMT

i have tryed it it was running but not ending it ran for 1 hrs i have tested on 10 excel files

Re: Import multiple excel file from a folder with dsetnames as excel names

sas_Forum — Wed, 13 Jun 2012 11:41:49 GMT

%let subdir=/data/inputfiles/;
filename dir "&subdir.*.xls ";

data new;
length filename fname $ 100;
infile dir eof=last filename=fname;
input ;
last: filename=fname;
run;

proc sort data=new nodupkey;
by filename;
run;
proc print data=new;
run;

data x;
set new;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.'), ,'ka'));

if last then call symputx('nobs',_n_);

run;
proc print data=x;
run;
%put user;

%macro import;

%do i=1 %to &nobs;

proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;

getnames=yes;

mixed=yes;

run;

%end;

%mend import;

%import;

536 %import;

WARNING: Apparent symbolic reference NOBS not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand

is required. The condition was:

&nobs

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro IMPORT will stop executing.

537

Getting this ERROR:

Re: Import multiple excel file from a folder with dsetnames as excel names

shivas — Wed, 13 Jun 2012 12:21:31 GMT

Hi,

Couple of changes to your code..just try this..

filename dir "&subdir/*.xls ";

data new;

length filename fname $ 100;

infile dir eof=last filename=fname;

input ;

last: filename=fname;

run;

proc sort data=new nodupkey;

by filename;

run;

proc print data=new;

run;

data x;

set new end=last;

call symputx(cats('filename',_n_),filename);

call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.'), ,'ka'));

if last then call symputx('nobs',_n_);

run;

Thanks,

Shiva