Create a 'look up' list for a where statement

[DME790]

Hi All,

At times I have large lists that I need to included in a Where statement (up too 387).

Is there a way I can create a list and reference that in the Where statement instead of having the individual code included in the statement?

In the example below remove all the AAA_ and reference a list that contains all of these.

Proc Sort data=Data.preagent out=Work.TrainingOverlay
	(Keep= week_end_dt CLUSTER Queue_CD Calls_Entered_cnt CALLS_ANSWERED_CNT);
	BY week_end_dt CLUSTER Queue_CD;
	Where week_end_dt >= '01Jul2016.'d
		and Queue_CD in (
						'AAA_ABR' 
'AAA_ABS' 
'AAA_ABS' 
'AAA_ACD' 
'AAA_ACE' 
'AAA_ACR' 
'AAA_ACY' 
'AAA_ADV' 
'AAA_AEC' 
'AAA_AEI' 
'AAA_AES' 
'AAA_AIN' 
'AAA_AIP' 
'AAA_ALD' 
'AAA_ALE' 
'AAA_ALN' 
'AAA_ALR' 
'AAA_ALY' 
'AAA_APR' 
'AAA_ARA' 
'AAA_ASR' 
'AAA_BOS' 
'AAA_BRA' 
'AAA_BVL' 
'AAA_BVL' 
'AAA_CAN' 
'AAA_CCB' 
'AAA_CCP' 
'AAA_CDT' 
'AAA_CHD' 
'AAA_CHK' 
'AAA_CHS' 
'AAA_CHY' 
'AAA_CLH' 
'AAA_CMC' );
run;

Cheers

Dean

[ChrisBrooks · Accepted solution]

Put your list into a pemanent reference table, then use proc SQL to build a string from that list and load it into a macro variable. You can then use the macro variable in your where clause like this

data list;
	length name $8;
	infile datalines;
	input name;
	datalines;
John
Judy
Mary
;
run;

proc sql;
	select quote(trim(name)) into :name_list
	separated by " "
	from list;
quit;


proc sort data=sashelp.class out=class;
	by name;
	where name in (&name_list);
run;

You only then need to update the table when and if your codes change.

[ballardw]

An extension on @ChrisBrooks if you have subsets of the values you use frequently would be to add other variables to indicate membership. Then select using that indicator:

data lookup;
   input Code $ Region type ;
datalines;
AAA_ABR 1 0
AAA_ABS 1 1
AAA_ABS 0 1
AAA_ACD 1 1
AAA_ACE 1 0
;
run;
proc sql;
	select quote(trim(code)) into :code_list
	separated by " "
	from lookup
   where Region=1;
quit;

If I used this very frequently and the list was relatively static I might consider a series of formats similar to:

proc format library=work;
value $FirstKeep
'AAA_ABR', 
'AAA_ABS', 
'AAA_ABS', 
'AAA_ACD', 
'AAA_ACE', 
'AAA_ACR', 
'AAA_ACY', 
'AAA_ADV', 
'AAA_AEC', 
'AAA_AEI', 
'AAA_AES', 
'AAA_AIN', 
'AAA_AIP', 
'AAA_ALD', 
'AAA_ALE', 
'AAA_ALN', 
'AAA_ALR', 
'AAA_ALY', 
'AAA_APR', 
'AAA_ARA', 
'AAA_ASR', 
'AAA_BOS', 
'AAA_BRA', 
'AAA_BVL', 
'AAA_BVL', 
'AAA_CAN', 
'AAA_CCB', 
'AAA_CCP', 
'AAA_CDT', 
'AAA_CHD', 
'AAA_CHK', 
'AAA_CHS', 
'AAA_CHY', 
'AAA_CLH', 
'AAA_CMC', 
   = 'Keep'
other ='Drop'
;
run;

and then use

Where week_end_dt >= '01Jul2016.'d
		and put(Queue_CD,$FirstKeep.)='Keep'

If the lists you want exist in data sets then they can likely be modified to create formats.