Hi all,
The macro below is what I am using to extract multiple monthly CSV files.
How can I also macro the 'Jan 15' within the infile location in the most efficient way. The space is causing issues.
%Macro Fst(mon);
data WORK.&mon. ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'Location\CSV\Jan 15.csv' delimiter = ',' MISSOVER
DSD lrecl=32767 firstobs=2 ;
informat key $10. ;
format key $10. ;
input key $;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;
%mend Fst;
%Fst(Jan15);
%Fst(Feb15);
...
%Fst(Jan18);
Given that you already have an existing macro variable, but need to add a space, here's one way. Notice the double quotes:
"Location\CSV\%substr(&mon,1,3) %substr(&mon,4,2).csv"
data WORK.&mon. ; -> becomes data WORK.Jan 15; -> which is invalid SAS code.
Pass Jan 15 to the macro and use %COMPRESS to remove the spaces.
data WORK.%compress(&mon. );
If there's not a %COMPRESS function use
data work.%sysfunc(compress(&mon.));
The data WORK.&mon. resolves correctly to WORK.Jan15 (no spaces) as required.
However, the infile statement is reading in a CSV file with a space 'Jan 15.csv' so I can't get that part to work within the macro.
Adding the below variable doesn't work
%FST (Jan15, Jan 15);
Any ideas?
Cheers
Your macro shows no attempt to use the macro parameter in the INFILE statement.
The line you show as
infile 'Location\CSV\Jan 15.csv' delimiter = ',' MISSOVER
maybe should look more like
infile "Location\CSV\&mon..csv" delimiter = ',' MISSOVER
the double quotes are needed to allow the macro variable to resolve, the two periods are needed, the first tells SAS that the macro variable is going to have some additional text that is not part of the macro variable and the second is the actual separator for the csv.
If you use &moncsv then SAS would expect a macro variable previously defined with the name moncsv.
If you use &mon.csv the result would be something like "Jan 15csv" which isn't your file name.
I have tried this solution but getting this error:
'Physical file does not exist'
It's because The CSV file has a space (Jan 15) as opposed to Jan15.
Do you have any idea on how I can get around this?
Either pass the macro two values. One to use the find the CSV file and one to use for the SAS dataset.
Or have the macro transform the value used to find the CSV file into a valid dataset name.
%macro readcsv(month);
%local dsname ;
%let dsname=%sysfunc(compress(&month));
data &dsname ;
infile "beginning&month.ending" dsd .... ;
...
%mend readcsv ;
%readcsv(Jun 15);
Given that you already have an existing macro variable, but need to add a space, here's one way. Notice the double quotes:
"Location\CSV\%substr(&mon,1,3) %substr(&mon,4,2).csv"
Perfect, thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: