BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PetePatel
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Reeza
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User Tom
Super User

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
PROC Star

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
Quartz | Level 8

Perfect, thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1046 views
  • 0 likes
  • 5 in conversation