request for simple macro to handle files

[ssas]

Hi,
i request you to help about :

i have 35 excel files,
i would like to import all files into sas

i am importing the files using the following code by changing the file path and name

%let snameout=ilgeagles;
PROC IMPORT OUT= mlspr09.&snameout
DATAFILE= "C:\fay\spring09 mails out\the illegal eagles spring09.csv"
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

but i need help in-regards to

1. A macro that can import the files from various locations into one specified libary awith one datastep
2. same macro can help me to prepare a dataset that should have all 35data sets together in it.
some think like
data target.required;
set source.&a &b &c...........;
run;


Many Thanks in-advance,
suresh

[sbb]

Clearly someone has put a bug in your ear that a macro approach is the only way -- I have to suggest otherwise. Also, you are asking for someone to do programming that you cannot - unless I'm misunderstanding your request?

As an alternative, consider the merits if a SAS DATA step approach, iteratively looping with a code such as (not involving macro logic, only SAS DATA step variables):

FILENAME TEMPSAS temp;
DATA _NULL_;
FILE TEMPSAS;
DO I=1 TO 35;
* GENERATE SAS CODE TO A TEMP FILE TEMPSAS. ;
* USE PUT STATEMENT LOGIC TO THE OPEN FILE AS NEEDED. ;
END
STOP;
RUN;
%INCLUDE TEMPSAS;
RUN;


Also, consider that if you avoid the macro approach, it may be a task you would consider undertaking -- possibly even one you could then support, modify, enhance going forward without having to depend on forum subscribers?

Scott Barry
SBBWorks, Inc.

[coba]

This is great about the forum support, to get solutions to problems that I myself are unable to solve. I really like this forum.

Thank you all that have helped me in the past

[Alankar]

I know this way.......

%macro import(path,file,dsn);
proc import out = &dsn
datafile = "&path..&file..csv"
dbms = csv replace;
getnames = yes;
datarow = 2;
run;
%mend import;

%import(c:\sas,employee,emp);
%import(c:\sas\data,salaries,sal);
%import(d:\programm,demographic,demo);
.
.
.
.

[ssas]

Thanks a lot alankar and sbb for your help

Thanks,
sams

[Tom]

If your CSV files have a known structure then it is easier to just read them directly into a dataset.

You can use a wildcard (*) in the file name to find all csv files in a directory.

%let dir=C:\fay\spring09 mails out;

data spring09 ;

   infile "&dir\*.csv" dsd dlm=',' truncover ;

   input var1 var2 .... ;

run;

You might need to add something to eliminate header lines when reading multiple files.

data spring09;

   infile "&dir\*.csv" dsd dlm=',' truncover;

   input @;

   if _infile_ =: 'var1' then delete;

  input var1 var2 ... ;

run;

[Ksharp]

The following code I wrote is a long time ago. I think you can use call execute() to optimize it.

%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

Ksharp

[jeeth79usa]

Hi,

I have similar question. But, I am trying to read multiple csv files from same folder with different file structure and files names in solaris environment. I need to check the files, and check if there are more than one row and then read the files every day, and append them.

I have created this macro to read the files. I would like to know if its possible to read all of them with a single macro.

Please advise if I am doing anything wrong in it. Thannks in Advance.

Mycode:

%let path = "/sasdata/path/";

%let libto = library;

options mprint;

%macro Readcsv (csvfile=);

/*   %let hasRows=;*/

   %let foundit = %sysfunc(exist(&path.&csvfile..csv));

   %if &foundit %then %do;

      filename csvTest "&path.&csvfile..csv";

      data _null_;

         infile csvTest;

         input;

         if _N_ eq 2 then do;

/*            call symput("hasRows", "yes");*/

            stop;

         end;

      run;

         %end;

          proc import datafile=&path.&csvfile..csv

            out=&libto..&csvfile replace;

            delimiter= ",";

            getnames=yes;

            scan=yes

            mixed=yes;

         run;

/*   %end;*/

%mend Readcsv;

%Readcsv (csvfile= file_xyz);

%Readcsv (csvfile= file_abc);

[Ksharp]

If your solaris environment support FILENAME + PIPE then the task will be very simple.

But I think call execute is a good one for your question.

%let path = "/sasdata/path/";

%let libto = library;

options mprint;

%macro Readcsv (csvfile=);

/*   %let hasRows=;*/

   %let foundit = %sysfunc(exist(&path.&csvfile..csv));

   %if &foundit %then %do;

      filename csvTest "&path.&csvfile..csv";

      data _null_;

         infile csvTest;

         input;

         if _N_ eq 2 then do;

/*            call symput("hasRows", "yes");*/

            stop;

         end;

      run;

         %end;

          proc import datafile=&path.&csvfile..csv

            out=&libto..&csvfile replace;

            delimiter= ",";

            getnames=yes;

            scan=yes

            mixed=yes;

         run;

/*   %end;*/

%mend Readcsv;

data _null_;

input fname $40. ;

call execute('%Readcsv(csvfile=',strip(fname),')');

cards;

file_xyz

file_abc

;

run;

Ksharp

[jeeth79usa]

Hello Shap,

Thannks for the suggestion. I am getting the following error when I used the call execute step. I tried different ways to accommodate the quotes, but still getting the same error. Can you please let me know if I am missing anything?

I have tried these

call execute('%Readcsv(csvfile=',strip(fname),')');

call execute('%Readcsv(csvfile=','||strip(fname)||');

/*call execute('%Readcsv(csvfile=','||strip(fname)||');');*/

ERROR 253-185: The EXECUTE subroutine call has too many arguments.

[Ksharp]

Sorry. It is my fault. I offer you the wrong code. It should use || instead of comma.

call execute('%Readcsv(csvfile='||strip(fname)||')');

or try to use cats() function:

call execute(cats('%Readcsv(csvfile=',fname,')'));

Ksharp