Infile statement Macro Spaces

[PetePatel]

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

[Reeza]

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

[PetePatel]

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

[ballardw]

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.

[PetePatel]

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?

[Tom]

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

[Astounding · Accepted solution]

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"

[PetePatel]

Perfect, thank you