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

[sas_Forum]

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

[Ksharp]

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

[sas_Forum]

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

           _

           22

           200

[Ksharp]

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

[sas_Forum]

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

[sas_Forum]

%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:

[shivas]

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