08-05-2016 12:44 PM
I'm running SAS Foundations 9.4 on a 64-bit Windows OS. I recently wrote a macro to run through a dataset and create macro variables containing lists of strings. Since these lists can get really long, I sometimes have to create 10, 20, even 40 lists to contain all the strings.
The goal was to be able to reference the lists in a where statement, e.g.
where keyvar in (&keyvarlist1, &keyvarlist2, &keyvarlist3);
I would like to be able to store the names of all of these macro variables in another macro variable; so, instead of having to say "where keyvar in (list-numbers-such-and-such)" I could just say, "where keyvar in (&masterlist)". The problem is, when I try to do this with a %let statement, it tries to store the contents of all of the lists in the one macro variable, and I get an error message about the string being too long.
Is it possible to create a macro variable listing other macro variables without SAS trying to read them all as one big character string?
08-05-2016 01:09 PM
First I would suggest that you describe what you are attempting to do. Many times creating that many lists of multiple values means the problem may be data structure related or program logic isn't quite clear.
Post log result with the code and the warning if practical (please as text, not a picture or word document if you want to attach a document).
It might be easier to create a data set of the values and then use a join on the values then to maintain such lists of string values.
08-05-2016 03:37 PM
I've only been using SAS for about a year, so there is probably a much more straightforward way to accomplish what I'm trying to do. I'm using a where statement in a datastep. I have the set statement in a do loop so I can concatenate large numbers of files. The only way I know to speed up the process is to use a key variable the way I'm doing it. Is there a better way?
Here is a generic example :
*creating a dummy set; data dummyset dummyset1 dummyset2 dummyset3; length id $7.; do i=1 to 30000; id=strip(put(i,$7.)); output dummyset dummyset1 dummyset2 dummyset3; end; run; %macro IDlist(listcount,iteration); *next several steps create N lists of key variables; data listID (keep=ID); n=_n_; set dummyset nobs=number_of_total_obs; if number_of_total_obs=1 then do; call symput('listcount','1'); call symput('iteration','1'); end; listsize=round(number_of_total_obs/&listcount)*(&iteration); lastlist=round(number_of_total_obs/&listcount)*((&iteration)-1); lengthsize=((round(number_of_total_obs/&listcount))*8)+100; call symput('lengthsize',cats("$",strip(lengthsize),".")); if lastlist < n <= listsize then output; run; %put &lengthsize; proc sort data=listID; by id; *put all key variables on one line in order to concatenate in next step; proc transpose data=listID out=listID prefix=ID; var ID; *create list; data _null_; length allIDs allIDs2 &lengthsize; set listid (keep=id:); array char_array [*] _character_; do i = 1 to dim(char_array); allIDs=catx("','",allIDs,strip(char_array[i])); end; allIDs2=cats("'",strip(allIDs),"'"); %global idlist&iteration; call symput("idlist&iteration",allIDs2); run; %mend IDlist; %macro makelists; %let a=15; %do b=1 %to 15; %IDlist(&a,&b); %end; %mend makelists; %makelists; %let start=1; %let stop=3; *run files; Data idtest; set %macro idloop; %do i=&start %to &stop %by 1; dummyset&i (keep=id where=(id in (&idlist5,&idlist6))) %end; %mend idloop; %idloop;; run;
08-05-2016 07:39 PM
You may be interested at looking into this to make single macro variables containing a list of words.
data list; input word $; datalines; abd defsg hiiiddd addaadd wewewe ; run; proc sql noprint; select word into : wordlist separated by ',' from list; select quote(strip(word)) into : qwordlist separated by ' ' from list; quit; %put Wordlist= &wordlist ; %put Qwordlist= &qwordlist;
08-06-2016 04:32 PM
Thanks. I've never used Proc SQL to create a list before. I will definitely use this in the future.
08-05-2016 01:55 PM
You may need to plan on a different approach. It is conceivable that SAS has a limit on the length of a single statement, and that you might exceed that limit.
By any chance, is this WHERE statement supposed to be part of a SQL SELECT statement? If it is, you might not need to create any macro variables. For example, this sort of approach might be feasible:
create table my_subset where keyvar in (select distinct keyvar from other_table);
If you're actually using a DATA step, there are still other methods such as creating a format or creating a hash table from your lists of strings. Add a little more context, and there are certain to be other approaches.
08-06-2016 08:47 PM - edited 08-06-2016 09:17 PM
Thank you! This solved my problem without the need to create numerous macrovariables.
*reference set to run test sets against; data refset; length id $7.; do i=1 to 30000; id=strip(put(i,$7.)); output; end; run; *test sets to test code against; data testset1 testset2 testset3; length id $7.; do i=1 to 1000; id=strip(put(round(ranuni(0)*1000),$7.)); if i < 300 then output testset1; else if 300 < i < 600 then output testset2; else output testset3; output; end; run; *list macro; %macro test; %let dsid=%sysfunc(open(refset)); %let cnt=%sysfunc(attrn(&dsid,nobs)); %do i=1 %to &cnt; %let rc=%sysfunc(fetchobs(&dsid,&i)); "%cmpres(%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,id)))))" %end; %let rc=%sysfunc(close(&dsid)); %mend test; %let start=1; %let stop=3; *run files based on key variable list; Data resultset; set %macro idloop; %do x=&start %to &stop %by 1; testset&x (keep=id) %end; %mend idloop; %idloop;; where id in (%test); run;
Need further help from the community? Please ask a new question.