/* 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

It does sound like a good paper, especially if the source code can be made public. ... View more

Recent email from SAS mentioned SAS 9.4 ts0m8, so traditional SAS will be around for at least another 5 years.     SAS is pushing everyone to Viya, and "we're not going to fix minor irritations in traditional SAS" is one way to push.  ... 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

If you want to create values that look meaningful but are in fact anonymized, and you want to do it right, you need a team of Computer Science PhD's, not one person doing it as a side project.   There are commercial products that do what you want: https://www.softwaretestinghelp.com/data-masking-tools/         ... View more

In SAS/Access to Oracle, there is a DBMAX_TEXT option that controls the maximum length, but there doesn't appear to be a similar option with Teradata, unless it's documented in a non-helpful way.   If you do the processing strictly in SAS code, without involving metadata at all, does the same problem occur?  It might help solve the problem if you can determine whether it's in SAS/Access or in the BI interface. ... View more

Can you mount the Mac file system over your network to Windows, and vice-versa?  You'd have to make some changes to your programs and workflow because drive names/letters would be different, but other than that the process should be transparent.   Our main storage is on a Unix machine, and mounted to Windows through our WAN.  If we're working in Unix from SAS Studio we might refer to a file as "~/mydir/myprog.sas", and from Windows we would refer to the same file as "O:\mydir\myprog.sas".  We can create spreadsheets from SAS in Unix and read them from Windows, and all the other normal things you might want a shared file system to do.   Apple uses a proprietary file system that is not supported in Windows by default.  I don't know whether the network file system will take care of that for you, or if you have to add another layer like Paragon's HFS+.   My new M1 MacBook hasn't arrived yet, so I can't test any of this.           ... View more

Does it have to be in PROC SQL? PROC CONTENTS will give you that information as well. ... View more

We have a similar problem, but unfortunately some peculiarity in how Solaris quotas work prevents them from being useful for work space overruns.  Or any kind of space policing, unfortunately.  So a few times the whole system has stopped working correctly because a rogue program has used up all of our terabytes of work space.   Redirecting WORK= or USER= (or both) to the user's home drive should be possible.  WORK= has to go into the configuration file, but USER= can go into either the configuration file or a program (or the autoexec).   Also, read up on UTILLOC, which is also used for some kinds of work files.  Depending on your setup, you might need to redirect that as well.   We have dedicated work drives that are faster than regular user drives, so for us redirecting WORK would cause a performance hit.  On Windows, we redirect WORK to a network drive so no protected health information is stored locally.  Slower, but we don't want users to run big jobs on Windows anyway,         ... 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

Sending email with images is so much easier than it used to be! ... 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