The SAS Output Delivery System and reporting techniques

Importing many files csv with macro

Reply
N/A
Posts: 0

Importing many files csv with macro

I try to import with many files ".csv" with the following macro but I couldn't´t because this data step has a mistake and I don´t know how i can arrange this mistake.

%macro import(name,direction);
PROC IMPORT OUT= &name
DATAFILE= &direction
DBMS=CSV REPLACE;
DELIMITER=';';
getnames=yes;
RUN;
%mend;

DATA _null_;
set maestro;
call execute ('%import(name='||file||',direction=' ||all||')');
run;

proc contents data=work._all_ out=lawork noprint;
run ;
proc sort data=lawork(keep=libname memname) nodupkey;
by memname;
where substr(memname,1,1)="A";
run;

Proc SQL noprint;
Select memname into:files separated by " " from lawork; quit;
%put &files;

Data all_files;
set &files;
SAS Super FREQ
Posts: 8,745

Re: Importing many files csv with macro

Hi:
This is not really an ODS-related question. Generally speaking, with SAS Macro programs, I always recommend starting with a working SAS program. When I write PROC IMPORT code, I generally write it like this:
[pre]

** step 1: Have a working SAS program;
proc import out=myfile1
datafile="C:\DataDir\myfiles\file1.csv"
dbms=csv
replace;
delimiter=';';
getnames=yes;
run;

or

filename mycsv "C:\DataDir\myfiles\file1.csv";
proc import out=myfile1
datafile=mycsv
dbms=csv
replace;
delimiter=';';
getnames=yes;
run;
[/pre]

So, then, if I were going to turn this program into a program that used macro variables, I would alter it to be something like this:
[pre]
** step2: Modify the working SAS program to use macro variables;
** that are set with %LET statements;

%let outsas=myfile1;
%let incsv = %str(C:\DataDir\myfiles\file1.csv);

proc import out=&outsas
datafile="&incsv"
dbms=csv
replace;
delimiter=';';
getnames=yes;
run;


or

filename mycsv "&incsv";

proc import out=&outsas
datafile=mycsv
dbms=csv
replace;
delimiter=';';
getnames=yes;
run;
[/pre]

Note the need for quotes around the physical data file name -- since it is a fully qualified operating system path and location. However, I do not need quotes around the output SAS data set name because SAS file names do not need to be quoted in code. You also do NOT need quotes around a FILEREF -- however, you do need quotes in a FILENAME statement. Once you have tested step 1 and then tested step 2, you now know where quotes are needed when you convert your program from step 2 to become a macro program (this would be step 3).

For help with PROC IMPORT or debugging macro code or data step problems, your best bet is to contact Tech Support. They can look inside your "MAESTRO" data set that is providing the file names for the CALL EXECUTE invocation of %IMPORT and advise you where you might be running into problems with your code.

To learn how to contact Tech Support, refer to:
http://support.sas.com/techsup/contact/index.html
cynthia

PS -- as a method of streamlining your code, you might consider the following alternative for the second half of the code (once your macro is working). It would allow you to eliminate your PROC CONTENTS step, the PROC SORT step and the PROC SQL step all in one alternative section of code, by using the automatic file DICTIONARY.TABLES (and produce the same results as the above 3 steps):
[pre]
proc sql noprint;
select distinct(memname) into :altfile separated by " "
from dictionary.tables
where substr(memname,1,1) = "A" and
libname = 'WORK'
order by memname;
quit;

%put &altfile;

Data all_file2;
set &altfile;
run;

[/pre]
New Contributor
Posts: 4

Re: Importing many files csv with macro

libname stat " C:\stat_data";


/* Use this macro to create SAS data sets from CSV files. Customizations are only needed in: */
/* 1) Datalog which is a listing of all csv files to be read into SAS. */
/* In Datalog create two columns with the name and path of the csv dataset */
/* e.g name Path */
/* abc C:\stat_data\abc.csv */
/* xyz C:\stat_data\xyz.csv */
/* 2) On the Path statement, replace the path name with the path of the csv file. you are reading from.*/
/* 3) Specify your libref where SAS data sets will be created in this case we chose Stat */
/* 4) By varying the values of the positional parameters n and index,you control how many csv files are to be read into SAS. */
/* IMPORTANT: CSV file names and variable names must conform to SAS naming conventions */
/* Remove delimiters LIKE ' , ' from data values .*/

options Symbolgen Mlogic NOMPRINT;
%let dsname = Datalog;
%let path1 = C:\stat_data\&&dsname..csv;
%let path2 = H:\..\stat_data\&&dsname..csv;
%let libto = STAT;

%Macro get_data(n,x,index); /* Start Macro*/
New Contributor
Posts: 4

Re: Importing many files csv with macro

/* check if datalog exists in dir, if it exists then import it from Excel and create SAS data set called datalog*/
%IF &n <= &index %then %Do;
%let foundit = %sysfunc(exist(&&path&x..&dsname));
proc import datafile= "&&path&x"
out=&libto..&dsname
dbms=dlm
replace;
delimiter=", ";
getnames=yes;
Title "Listing of CSV Files In &dsname As of &sysdate";
proc print data=&libto..&dsname;
run;
Title " ";

/* This part of the macro uses DO While to loop through each name, path of csv */
/* files and extract the data sets specified by the upper limit INDEX */
/* and the lower limit n */

%DO %WHILE (&n <=&index);
data _NULL_;
set &libto..&dsname end=last;
if _N_ =&n then Do;
call symput(COMPRESS('path'),Path); /* Get file path*/
call symput(COMPRESS('dsn'),Name); /* Get dataset name*/
if not last then index+1; /* if not last record then increment index by 1 on each iteration*/
else if last then call symput ('index',index); /* if last record then call the macro variable index*/
End;
run;
proc import datafile= "&path"
out= &libto..&dsn
dbms=dlm
replace;
delimiter=", " ;
getnames=yes;
run;
Title "Data set &dsn";
proc print data=&libto..&dsn;
run;
Title " ";
%let n=%eval(&n+1);
%let log1 = C:\log\statlog.txt; /* output the log to log1*/
%let log2 = H:\..\log\statlog.txt; /* or output the log to log2*/
proc printto log="&&log&x" new ;
run;
%End;
%End;
/* Write Error Message to the log if &n gt &index"*/
Data _NULL_;
set &libto..&dsname;
%IF &n gt &Index %then %Put ERROR Ending execution: Check values of n and Index (n must be less than or equal to index);
run;
%Mend get_data;

/* Macro Call*/

%get_data(8,1,5);
New Contributor
Posts: 4

Re: Importing many files csv with macro

> I try to import with many files ".csv" with the
> following macro but I couldn't´t because this data
> step has a mistake and I don´t know how i can arrange
> this mistake.
>
> %macro import(name,direction);
> PROC IMPORT OUT= &name
> DATAFILE= &direction
> DBMS=CSV REPLACE;
> DELIMITER=';';
> getnames=yes;
> RUN;
> %mend;
>
> ATA _null_;
> set maestro;
> call execute ('%import(name='||file||',direction='
> ||all||')');
> run;
>
> roc contents data=work._all_ out=lawork noprint;
> run ;
> proc sort data=lawork(keep=libname memname)
> nodupkey;
> by memname;
> where substr(memname,1,1)="A";
> run;
>
> roc SQL noprint;
> Select memname into:files separated by " " from
> lawork; quit;
> %put &files;
>
> ata all_files;
> set &files; Message was edited by: leo_36
Ask a Question
Discussion stats
  • 4 replies
  • 274 views
  • 0 likes
  • 3 in conversation