SAS Macro issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

SAS Macro issue

I'm having trouble with some macro code that works when I use it similarly using a list of state names but fails when I use it in the context below.  I'm using SAS 9.4 TS Level 1M2 x64_7 platform. 

 

Not sure what I'm missing if anyone can help me it would be much appreciated!

 

%macro dataimport(year);

data sob&year;

infile "F:\Bermuda\Agriculture\Market Data\US\sobscc&year..txt" dlm='|' lrecl=170;

input year :4. sfips :$2. state :$2. cfips :$3. county :$30. commoditycode :4. commodity :$30. plancode :2. plan :$5. covcat :$5. policiessold :8. policiesearn :8. policiespaid :8. unitearning :8.

unitspaid :8. acres :10. liability :10. premium :10. subsidy :10. claim :10. lossratio :4.2;

run;

data sob&year;

set sob&year;

year=&year;

run;

%mend dataimport;

*loops through all of the comma separated "values" to read in all of the historical information;

%macro readloop(values);

 

/* Count the number of values in the string */

%let count=%sysfunc(countw(&values));

/* Loop through the total number of values */

%do i = 1 %to &count;

%let value=%qscan(&values,&i,%str(,));

%dataimport(&value);

%put "&value read";

%end;

 

%mend;

%readloop(%str(80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,00,01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16));

quit;

 

When I run this I get the following error.

NOTE: Line generated by the macro variable "YEAR".

1 sob80

--

22

--

200

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;,

_DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.SOB may be incomplete. When this step was stopped there were 0

observations and 21 variables.

WARNING: Data set WORK.SOB was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

 

 

 

If I run just the guts of the data import macro it works ie. the below works so it has something to do with the readloop macro.  Which works in a different program when pass it comma separated state names.

 

%let year=01;

data sob&year;

infile "F:\Bermuda\Agriculture\Market Data\US\sobscc&year..txt" dlm='|' lrecl=170;

input year :4. sfips :$2. state :$2. cfips :$3. county :$30. commoditycode :4. commodity :$30. plancode :2. plan :$5. covcat :$5. policiessold :8. policiesearn :8. policiespaid :8. unitearning :8.

unitspaid :8. acres :10. liability :10. premium :10. subsidy :10. claim :10. lossratio :4.2;

run;

data sob&year;

set sob&year;

year=&year;

run;

 


Accepted Solutions
Solution
‎08-19-2016 11:11 AM
Super User
Super User
Posts: 6,499

Re: SAS Macro issue

[ Edited ]

The macro quoting is confusing SAS so that it is treating SOB&YEAR as two tokens.

 

Use %SCAN() instead of %QSCAN() in the outer macro.  There is no need to quote it when passing it to the inner macro.

 

In general it is much easer to pass SPACE delimited lists instead of COMMA delimited lists.  The only place in SAS that likes commas is SQL and you could easily put them back if you need them.

 

%let value=%scan(&values,&i,%str( ));
%dataimport(&value);
...
%readloop(80 81 82 83 84);

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,399

Re: SAS Macro issue

Sorry, not reading all that.  To summarise you have a set of files, all the same strucure in a directory with just the datepart different?  If so then just do:

data total;
  infile "<path to files>\*.txt" dlm="|" lrecl=170 filename=fname;
  input...;
run;

This will give you one dataset with all the data imported, and with a variable called fname which indicates the filename of the incoming data.  If you really need to (and I never recommend it) split the data then, you can just split the data out into datasets based on that variable.  However as SAS is built to work on by groups, you would be better off processing that variable to get date out, and then using a proper date variable to by group on.

 

Respected Advisor
Posts: 3,777

Re: SAS Macro issue


RW9 wrote:

Sorry, not reading all that.  To summarise you have a set of files, all the same strucure in a directory with just the datepart different?  If so then just do:

data total;
  infile "<path to files>\*.txt" dlm="|" lrecl=170 filename=fname;
  input...;
run;

This will give you one dataset with all the data imported, and with a variable called fname which indicates the filename of the incoming data.  If you really need to (and I never recommend it) split the data then, you can just split the data out into datasets based on that variable.  However as SAS is built to work on by groups, you would be better off processing that variable to get date out, and then using a proper date variable to by group on.

 


 

The variable FNAME will NOT be added to the output data as you suggest you must take the extra step to assign it to a "user" variable.

 

Perhaps

source = fname;

 

 

 

 

Occasional Contributor
Posts: 9

Re: SAS Macro issue

Thanks for your help that is actually a better solution reading in files with similar structure and systematically differing names than my macro loop.

 

I figured out that the issue my code is that I need to use %unquote(&value) in my call to the dataimport macro from the readloop macro

 

ie. replace %dataimport(&value); with %dataimport(%unquote(&value)); 

 

Apparently the text passed into the macro retains some sort of special status even after I've broken up the string and this can cause odd results unless the status is removed with a %unquote

Occasional Contributor
Posts: 9

Re: SAS Macro issue

Thank you this works as well!

Occasional Contributor
Posts: 9

Re: SAS Macro issue

This doesn't quite work as fname is defaulted to $8. and filename includes the whole path.  Is there somewhy to set fname to accept a longer string?

Respected Advisor
Posts: 3,777

Re: SAS Macro issue

Use a LENGTH statement before the INFILE statement to give FNAME a length long enough to hold the entire file path.

 

Use SUBSTR or other character functions to extract the year from the name.

Occasional Contributor
Posts: 9

Re: SAS Macro issue

Thanks that's got it!
Solution
‎08-19-2016 11:11 AM
Super User
Super User
Posts: 6,499

Re: SAS Macro issue

[ Edited ]

The macro quoting is confusing SAS so that it is treating SOB&YEAR as two tokens.

 

Use %SCAN() instead of %QSCAN() in the outer macro.  There is no need to quote it when passing it to the inner macro.

 

In general it is much easer to pass SPACE delimited lists instead of COMMA delimited lists.  The only place in SAS that likes commas is SQL and you could easily put them back if you need them.

 

%let value=%scan(&values,&i,%str( ));
%dataimport(&value);
...
%readloop(80 81 82 83 84);

 

Super User
Posts: 10,483

Re: SAS Macro issue

<Rant mode on>

Did no one learn anything from the Y2K nonsense? Still naming things with 2 digit years and crossing a century boundary.

 

<Rant mode off>

Occasional Contributor
Posts: 9

Re: SAS Macro issue

The targets are US federal government data files.  Need I say more

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 543 views
  • 2 likes
  • 5 in conversation