/* Create a SAS data set containing words to be looked for and */ /* their associated variables. */ data mylist; input @1 lookfor $32. @34 varname $32. ; datalines4; PHYSICAL physcomp ACTIVITIES makeract GAME gamedesign DATA datasci CODING coding MOBILE mobiledev ;;;; /*** Method 1 ***/ /* Create global macro variables containing expanded parts of array statements. */ %global lookfor_list varname_list list_length; proc sql noprint; select quote(trim(lookfor), "'") , trim(varname) into :LOOKFOR_LIST separated by ' ' , :VARNAME_LIST separated by ' ' from mylist; %let list_length = &SQLOBS. ; quit; %put INFO: &=LOOKFOR_LIST ; %put INFO: &=VARNAME_LIST ; %put INFO: &=LIST_LENGTH ; data _null_; array keywords [&LIST_LENGTH.] $32 _temporary_ (&LOOKFOR_LIST.) ; array varnames [&LIST_LENGTH.] 4 &VARNAME_LIST. ; put keywords[1]=; run; %symdel lookfor_list varname_list list_length; /*** Method 2 ***/ /* create global macro variables containing complete array statements. */ %macro make_code(); %global lookfor_code varname_code; %local lookfor_list varname_list list_length; proc sql noprint; select quote(trim(lookfor), "'") , trim(varname) into :LOOKFOR_LIST separated by ' ' , :VARNAME_LIST separated by ' ' from mylist; %let list_length = &SQLOBS. ; quit; %let lookfor_code = array keywords [&LIST_LENGTH.] $32 _temporary_ (&LOOKFOR_LIST.) ; %let varname_code = array varnames [&LIST_LENGTH.] 4 &VARNAME_LIST. ; %put INFO: &=LOOKFOR_CODE; %put INFO: &=VARNAME_CODE; %mend make_code; %make_code(); data _null_; &LOOKFOR_CODE. ; &VARNAME_CODE. ; put keywords[1]=; run; %symdel lookfor_code varname_code; /*** Method 3 ***/ /* Create a macro that inserts code generated from SAS data set, */ /* without causing a section break. In real life, this would be */ /* parameter-driven. */ %macro use_dosub(); %local lookfor_code varname_code; %local lookfor_list varname_list list_length; %let dosubl_rc = %sysfunc(dosubl(%nrstr( proc sql noprint; select quote(trim(lookfor), "'") , trim(varname) into :LOOKFOR_LIST separated by ' ' , :VARNAME_LIST separated by ' ' from mylist; %let list_length = &SQLOBS. ; quit; %let lookfor_code = array keywords [&LIST_LENGTH.] $32 _temporary_ (&LOOKFOR_LIST.) ; %let varname_code = array varnames [&LIST_LENGTH.] 4 &VARNAME_LIST. ; ))); %put INFO: RC from &=DOSUBL_RC; %put INFO: &=LOOKFOR_CODE; %put INFO: &=VARNAME_CODE; &LOOKFOR_CODE; &VARNAME_CODE; %mend use_dosub; data _null_; %use_dosub(); put keywords[1]=; run; %put _user_; This is using a sledgehammer for a penny nail, but sometimes the ability to create code inline without generating a section break is very useful.  ... View more

Personally, I find it easier to maintain a long narrow list than a short wide list, and if the list is coming from an external source it's usually easier to read it in a data step than in a macro. Data-driven instead of manually maintained array statements. Using DOSUBL lets me do the processing right where the list of values is needed, so I don't have to create global macro variables. Depending on how much parameterization is needed, it might or might not be better than a macro. ... View more

Unnecessarily putting code inside a macro is so common that there must be a flaw in how the macro language is taught. If you had dozens or hundreds of keywords instead of half a dozen, it might be better to put the keywords and corresponding variable names into a SAS data set, and use a macro to generate the two ARRAY statements. DOSUBL might be even easier. Also, you might want to look at the R or T modifier in the FINDW function as an alternative to using the TRIM function. ... View more

Before writing a macro, you should figure out how to do it in open code.  That doesn't look like a problem where a macro will get you much more than parameterization.   If the solution doesn't have to be blazingly fast, you can do it in SQL.  Search for "moving average in sql" in Google.  Some of the solutions you'll find use features that aren't available in SAS's dialect of SQL.   Also, make sure you know what you mean by "year".  365 days ago?  365 days ago except in leap years when it's 366 days ago?  Same day of the same month in the previous year?  Same day of the same month in the previous year, but force it to a weekday?  Or to a working day?     ... View more

@PaigeMiller I don't understand why you think it's an unrelated question. The original question was about determining the values possible for a column reference in CALL DEFINE, and my question is also about that. And the answer to my question might provide an alternative answer to the problem behind the original question. ... View more

Is there a way to find the absolute column numbers of columns without preprocessing the data? Just to make up a simple example of when that might be useful, let's suppose I have some id columns, and then across columns for a variable, then a summary column, and I want to make the last across column pink. If I knew the column number of the final summary column, I could find the column number of the last across column by subtracting 1. I have occasionally had reason to run a PROC SUMMARY on data, and then PROC REPORT on the summary output. It surprised me that sometimes with large data sets the SUMMARY+REPORT approach was noticeably faster than creating the same final report using PROC REPORT alone. I would have thought the run times would be very similar because the same calculation engine is used in both. ... View more

They say that a feature is just a bug that has been documented.   But still think this is a bug.  The parser should be able to figure this out, and if it can't, no telling what other subtle bugs might be hidden in there.   ... View more