How can I write a macro that reads a list of variables?

[smilingmelbourne]

I have written the codes below (which is not good as it is too cumbersome) to append datasets based on given months and years. How can I modify the codes using a better macro?

%macro append(month=, year=);
proc append base=ds1 data=ds_&month._&year;
run;
%mend append;

%append(month=Mar, year=1997)
%append(month=June, year=1997)
......
.....
%append(month=Dec, year=2009)
%append(month=Mar, year=2010)

The problem is I have to write too many %append for each quarter and year. I would like to create a list of months and years like: Mar, June, Sept, Dec. For years, 1997, 1998, ..., 2010. But I don't know how a macro can interprete this in a do loop. Can you please help?

[ArtC]

Here is a simple macro that will loop through a series of months and years. You can easily adapt it to subsets of months etc.
[pre]%macro dates;
%local months year month mon;
%let months = Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
%do year = 1997 %to 2010 %by 1;
%do month = 1 %to 12 %by 1;
%append(month=%scan(&months,&month),year=&year)
%end;
%end;
%mend dates;
%dates
[/pre]

[smilingmelbourne]

Thank you all for helping. Based on ArtC's suggestion, I wrote the code below but somehow it doesn't resolve to the name of months. I'm just beginning to learn macros, so I'm finding it confusing when using the array.


/*Read in historical prices*/
options symbolgen;
%macro read_price;
%local year month;
%let month = Mar Jun Sep Dec;
%do year = 1997 %to 2009 %by 1;
%do month = 1 %to 4 %by 1;
proc import out=arc9710.price_hist_&month._&year
datafile="C:\Documents and Settings\XPMUser\My Documents\Quarterly Full History Feed\price_hist_&month.-&year..out"
dbms=dlm
replace;
delimiter='|';
getnames=no;
run;
%end;
%end;
%mend read_price;
%read_price

It just keeps resolving to something like "price_hist_4-1999.out, whereas I'd like to see "price_hist_Apr-1999". Can you please help? Thanks so much in advance

[sbb]

Investigate SAS to generate the macro variable in a %LET statement with the month prefix - using %SYSFUNC and PUT with the MONNAME3. format specification.

Example using today's date:

1 %put %sysfunc(putn(%sysfunc(today()),monname3.));
Aug


Scott Barry
SBBWorks, Inc. Message was edited by: sbb

[Cynthia_sas]

Hi:
Why did you take the %SCAN out of the code that Art posted for you??? Peter is probably right that you could solve this without using macro. However, if you REALLY are going to go down the macro road, it will be to your benefit to take a pause and LEARN what macro is all about, learn about how the %LET statement works and learn how the various macro functions work.

Otherwise, you are programming in the dark and no amount of advice from the forums can undo a broken program if you do not understand how SAS Macro programming and the SAS Macro variables work.

This paper is a good introduction:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

In addition, there are MANY, MANY previous forum postings on the topic of macro programming (including my many curmudgeonly explanations on how to develop a macro program by starting with a WORKING non-macro-ized SAS program, such as this one from 2008 http://support.sas.com/forums/thread.jspa?messageID=15938㹂 ).

And, you can Google for the MANY user group papers and tutorials that have been presented on SAS Macro processing and which are publicly available in various conference proceedings. I would recommend that you stick with BEGINNER tutorials and get those under your belt before you move into the more advanced tutorials.

cynthia

[Peter_C]

why do so much macro programming?
Probably you can achieve a solution with a datastep view and jusr one append proc.
If there is no data set to start with, all you need is a single data step.
These solutions need only a list of the input data sets.
data view/view=view; set &list; run;
proc append base=base data=view; run;

Knowing the input selection rules would help. Creating the list from dictionary tables sounds reliable and where clauses can restrict the generated list to just the date ranges required.
select cats( libname,'.',memname) into :list separated by ' '
from dictionary.tables where libname='YOURLIB' and memname EQT 'PREFIX'
and substr(memname,4,3) in('APR', 'JUN', 'SEP','NOV') and substr(memname,9,3) between '1997' and '2010'

[deleted_user]

i agree

[Peter_C]

smilingmelbourne,
why do so much macro programming?
Probably you can achieve a solution with a datastep view and just one append proc.
If there is no data set to start with, all you need is a single data step.
These solutions need only a list of the input data sets.
data step.
These solutions need only a list of the input data sets.
data view/view=view; set &list; run;
proc append base=base data=view; run;

Knowing the input selection rules would help. Creating the list from dictionary tables sounds reliable and where clauses can restrict the generated list to just the date ranges required.
select cats( libname,'.',memname) into :list separated by ' '
from dictionary.tables where libname='YOURLIB' and memname EQT 'PREFIX'
and substr(memname,4,3) in('APR', 'JUN', 'SEP','NOV') and substr(memname,9,3) between '1997' and '2010'

hope that gives you the idea.

[smilingmelbourne]

Thank you all very much! The SUGI papers are very useful to me and doesn't take much time to get a general idea (I am afraid of thick books).

May I continue to ask a question about the loop in SAS? In SAS, is there some sort of BREAK statement within a loop? For example, in the code below I want to stack up files upon files except the file has data for March 1997. Could you please help?

/*Stack up one after the other*/
%macro stack_up;
%local months month year;
%let months=mar jun sep dec;
%do year=1997 %to 2009 %by 1;
%do month=1 %to 4 %by 1;
%if year=1997 and %scan(&months,&month)=mar %then %do;
/*Something like BREAK here to quit this iteration, but don't know which*/
%end;
%end;
%mend stack_up;

[ArtC]

Yes this is possible and there is, of course, more than one way to do this.

From your question it is unclear to me what exactly you want to have happen when the condition is met. Stop execution for this particular combination of macro variable values, stop the loop, stop the macro, stop the program. Lots of choices.

Assuming that you are trying to control for this particular combination, perhaps the simplest is to use the %IF-%THEN/%ELSE. Also in your %IF you have left off the & for the macro variable &YEAR.

> /*Stack up one after the other*/
> %macro stack_up;
> %local months month year;
> %let months=mar jun sep dec;
> %do year=1997 %to 2009 %by 1;
> %do month=1 %to 4 %by 1;
> %if year=1997 and %scan(&months,&month)=mar %then %do;
> /*Something like BREAK here to quit this iteration, but don't know which*/
> %end;
> %end;
> %mend stack_up;

Your %IF will be something like the following (depending on what you want to do - or not do for the stated condition.
[pre]
%if &year=1997 and %scan(&months,&month)=mar %then %do;
/* Assumes that you want to do something here and not stop the loop*/
%end;
%else %do;
/* what you want to do for other conditions goes here */
%end;
[/pre]

For other what-if scenarios consider the %ABORT and %RETURN statements. Or other %IF-%THEN logic.

[smilingmelbourne]

Hi ArtC, thanks for your help. I re-wrote the code, but somehow it doesn't skip March/1997. What I want is like below:

/*Stack up one after the other*/
%macro stack_up;
%local months month year;
%let months=mar jun sep dec;
%do year=1997 %to 2009 %by 1;
%do month=1 %to 4 %by 1;%if year=1997 and %scan(&months,&month)=mar %then %do;
DO NOTHING! /*If the file is for 03/1997, then SKIP/DO NOTHING and Jump to June/1997 and continue on*/
%end;
%end;
%mend stack_up;

The code I re-wrote is below (it stacks mar/1997 files without skipping it)

/*Stack up one after the other*/
%macro stack_up;
%local months month year;
%let months=mar jun sep dec;
%do year=1997 %to 2010 %by 1;
%do month=1 %to 4 %by 1;
%if %scan(&months,&month)=mar and year=1997 %then %abort; /*No effect, still stack mar/1997 onto the base file*/
%else %do;
%append(month=%scan(&months,&month), year=&year)
%end;
%end;
%end;

%mend stack_up;

[Robert_Bardos]

If it's not a typo in posting to this forum change year to &year in your %if statement.

Robert

[ArtC]

Syntax is important. As Robert pointed out you have again dropped the &, which is essential to identify the macro variable &YEAR. Without the & the portion of the expression year=1997 will always be false and the %ABORT will not be executed.

Are you sure that %ABORT will do what you want?

[smilingmelbourne]

Thanks. The %ABORT is actually not what I want. So I've tried this

/*Stack up one after the other*/
%macro stack_up;
%local months month year;
%let months=mar jun sep dec;
%do year=1997 %to 2010 %by 1;
%do month=1 %to 4 %by 1;
%if %scan(&months,&month) ^= mar and &year ^=1997 %then %do;
%append(month=%scan(&months,&month), year=&year)
%end;
%end;
%end;

%mend stack_up;

That is, the stacking up of files upon files will occur only if the data set is not for Mar/1997. However, when checking the SAS log I still see that the March/1997 data set is appended to the base data set. Actually, I initially tried this one but it didn't work, so I thought of using some statement that is to skip the current iteration and move on to the next. I have referred to the SAS macro bible "Carpenters's Guide to SAS Macro...". There, I found %ABORT, %RETURN, but these two are not for the purpose. How should I modify the logic so that it works for my purpose?

/*Skip data set for March/1997*/
Scan through data sets from 1997 through 2010
Each year has 4 quarters: mar, jun, sep, dec
If the data set is for mar/1997, then SKIP
Otherwise, stack each of the data sets upon the base data set


Thanks so much

[smilingmelbourne]

I changed the logic as below and now it works!

/*Stack up one after the other*/
options symbolgen;
options mcompilenote=all;
options mprint;
options mlogic;
%macro stack_up;
%local months month year;
%let months=mar jun sep dec;
%do year=1997 %to 2010 %by 1;
%do month=1 %to 4 %by 1;
%if &year =1997 %then %do;
%if %scan(&months,&month) ^= mar %then %do;
%append(month=%scan(&months,&month), year=&year)
%end;
%end;

%else %do;
%append(month=%scan(&months,&month), year=&year)
%end;
%end;
%end;
%mend stack_up;
%stack_up


My question now is, where is the difference between

(1) %if %scan(&months,&month) ^= mar and &year ^=1997 %then %do;
%append(month=%scan(&months,&month), year=&year)

(2) %if &year =1997 %then %do;
%if %scan(&months,&month) ^= mar %then %do;
%append(month=%scan(&months,&month), year=&year)
%end;
%end;

In the first one, I think that it should skip the appending iteration if the month is March AND the year is 1997; however, the log shows that it skips everything in the year 1997 AND also it skips March for every year from 1997 through 2010.

In the 2nd, it works just fine.