BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mike_B
Obsidian | Level 7

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?

 

Thanks

 

Mike

1 ACCEPTED SOLUTION
7 REPLIES 7
ballardw
Super User

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.

Mike_B
Obsidian | Level 7

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;

 

Thank you

ballardw
Super User

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;
Mike_B
Obsidian | Level 7

Thanks. I've never used Proc SQL to create a list before. I will definitely use this in the future.

Astounding
PROC Star

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.

Mike_B
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3074 views
  • 4 likes
  • 4 in conversation