Infile statement Macro Spaces

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Infile statement Macro Spaces

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


Accepted Solutions
Solution
‎03-16-2018 05:58 AM
Super User
Posts: 6,781

Re: Infile statement Macro Spaces

Posted in reply to PetePatel

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"

View solution in original post


All Replies
Super User
Posts: 23,754

Re: Infile statement Macro Spaces

Posted in reply to PetePatel

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

 

Contributor
Posts: 26

Re: Infile statement Macro Spaces

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

Super User
Posts: 13,563

Re: Infile statement Macro Spaces

Posted in reply to PetePatel

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.

Contributor
Posts: 26

Re: Infile statement Macro Spaces

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?

Super User
Super User
Posts: 8,115

Re: Infile statement Macro Spaces

Posted in reply to PetePatel

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);
Solution
‎03-16-2018 05:58 AM
Super User
Posts: 6,781

Re: Infile statement Macro Spaces

Posted in reply to PetePatel

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"

Contributor
Posts: 26

Re: Infile statement Macro Spaces

Posted in reply to Astounding

Perfect, thank you

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 200 views
  • 0 likes
  • 5 in conversation