topic Re: request for simple macro to handle files in SAS Procedures

request for simple macro to handle files

ssas — Wed, 01 Apr 2009 13:17:16 GMT

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

Re: request for simple macro to handle files

sbb — Wed, 01 Apr 2009 15:03:50 GMT

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.

Re: request for simple macro to handle files

Alankar — Thu, 02 Apr 2009 02:53:31 GMT

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);
.
.
.
.

Re: request for simple macro to handle files

ssas — Thu, 02 Apr 2009 15:38:35 GMT

Thanks a lot alankar and sbb for your help

Thanks,
sams

Re: request for simple macro to handle files

coba — Tue, 22 Nov 2011 20:34:47 GMT

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

Re: request for simple macro to handle files

Tom — Wed, 23 Nov 2011 00:42:22 GMT

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;

Re: request for simple macro to handle files

Ksharp — Wed, 23 Nov 2011 02:21:04 GMT

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

Re: request for simple macro to handle files

jeeth79usa — Tue, 13 Mar 2012 01:19:25 GMT

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);

Re: request for simple macro to handle files

Ksharp — Tue, 13 Mar 2012 03:22:34 GMT

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

Re: request for simple macro to handle files

jeeth79usa — Fri, 16 Mar 2012 19:29:38 GMT

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.

Re: request for simple macro to handle files

Ksharp — Mon, 19 Mar 2012 02:46:49 GMT

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