BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
japfvg
Fluorite | Level 6
Hi all, hope everyone is doing great!!
Again asking for your precious help
 
I've this macro for importing one single file at once that works well:
%macro Carga(File);
data work.q1;
infile &File. dlm='|' firstobs=2 missover;
input field1 : $char5.
field2 : $char5.;
run;
%mend;
I have also a dataset containing all the csv files in a folder (the dataset is called "work.files" and the field with the csv file names is called "filename") which I need to load with the previous macro, I'm trying with this code but it's not working:
data _null_;
set work.files;
call execute(cats('%nrstr(%Carga(',filename,'))'));
run;
This last code was recomended by some of you guys for another problem (the parameters were date type instead of filenames) and worked very well so I was trying to create a similar code for this new challenge.
 
Any help would be greatly appreciated.
 
Thanks in advance.
 
Regards to all!!
 
Fer
 
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

If your goal is the import of all csv files into a single dataset, then no macro is needed. You can use your files dataset to supply filenames to the INFILE statement with the FILEVAR= option.

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

Your macro has a parameter called File which you never use in the macro.

Instead you use a macro variable called Archivo, which is not set in your code (as posted).

Your macro also uses a fixed name for the dataset, so after using the macro N times, only the Nth import will be stored in dataset q1. You need to devise a way of either feeding the dataset name to the macro via a second parameter, or to create a (valid) dataset name from the infile name within the macro.

 

So let's bo back to square one: post the data step code that successfully imports a single file (without any macro code), and sufficient examples for the contents of your dataset files.

japfvg
Fluorite | Level 6
Thanks Kurt for the comment, I just changed the macro code with the same variable...

And you're right, there is another part of the code which will append all the CSV files but it has not been written yet so for now it will take only the last file.

The second code it's what's not working with now and what's giving me trouble at this point.

Thanks again

Fer
Kurt_Bremser
Super User

If your goal is the import of all csv files into a single dataset, then no macro is needed. You can use your files dataset to supply filenames to the INFILE statement with the FILEVAR= option.

singhsahab
Lapis Lazuli | Level 10

@japfvg Below code may help you

 

%LET MAIN_PATH=KEEP PATH HERE;

DATA have;
 RC=FILENAME("FILEREF","&MAIN_PATH.");
 MYDIR=DOPEN("FILEREF");
 IF MYDIR > 0 THEN 
    NUM=DNUM(MYDIR);
	 DO I=1 TO NUM;
			IF I > 0 THEN DO
			   FILE_NAME=DREAD(MYDIR,i);
			   OUTPUT;
	END;
	END;
	rc=dclose(mydir);
RUN;
DATA have1;
SET have;
extn=STRIP(SCAN(FILE_NAME,-1,'.'));
if extn eq 'csv';
RUN;
PROC SQL ;
SELECT FILE_NAME INTO :SUBFILE1 -  FROM HAVE1;
%LET N=&SQLOBS;
QUIT;
%LET DATA=DATASETNAME;

%MACRO CSV_ALL;
%DO I=1 %TO &N.;
PROC IMPORT DATAFILE="&MAIN_PATH\&&SUBFILE&I"
DBMS=CSV REPLACE OUT=&DATA&I;
RUN;
%END;
%MEND;

%CSV_ALL;


Tom
Super User Tom
Super User

No need for macro code.  Just use a single data step to read all of the text files at once.

data WANT;
  set work.files;
  fname = filename ;
  infile text dsd  dlm='|' truncover firstobs=2 filevar=fname end=eof;
  do while(not eof);
    input field1 :$5. field2 :$5. field3 ;
    output;
  end;
run; 
japfvg
Fluorite | Level 6

Thanks to all for your quick responses!!

 

I tested the filevar option using this:

data work.q1;
set work.files1;
fname = filename;
infile text filevar=fname end=eof dlm=';' firstobs=2 dsd missover;

do while(not eof);
	input
		eoddate : ddmmyy11.	
		numberx : $char10.
		stgeneral : $char4.
end;

run;

Dataset work.files1 contains two records of two csv files which are needed to be imported.

 

After running the whole code, for some reason, the input with the filevar option is reading only the first record of every csv file.

 

Any idea why is happening this?

 

Thanks again for all your support.

 

Fer

 

Kurt_Bremser
Super User

Look closely at codes posted. Your code misses the explicit OUTPUT statement in the DO loop, so you only have one implicit output at the end of the data step iteration, outputting the last record read from a file.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1666 views
  • 5 likes
  • 4 in conversation