BookmarkSubscribeRSS Feed
ssas
Calcite | Level 5
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
10 REPLIES 10
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5

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
Fluorite | Level 6
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
Calcite | Level 5
Thanks a lot alankar and sbb for your help

Thanks,
sams
Tom
Super User Tom
Super User

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1455 views
  • 2 likes
  • 7 in conversation