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
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.