I have a stored process that has a parameter that is a string that is delimeted using the | character
the proc sql works fine if the var &l1=Exempt however if &l1=Exempt|General I want to be able to parse the string in
&l1 so that it ends up being
Exempt","General
so that when it gets to the Delete clause I have
DELETE FROM WORK.HRIR99991 WHERE BUSINESS_UNIT_DESC NOT IN ("Exempt","General");
how would i do that?
here is the proc sql code
%IF "&l1" ne "All" %THEN %DO;
PROC SQL;
DELETE FROM WORK.HRIR99991 WHERE BUSINESS_UNIT_DESC NOT IN ("&l1");
RUN;
%END;
In the attached I am creating a secondary macro variable which holds a transformed version of the condition you want fulfilled. Basically we are using Tranwrd to replace the | with "," and then adding the opening and closing quotation marks.
I hope this is useful.
%LET l1 = Exempt|General;
data HRIR99991;
LENGTH BUSINESS_UNIT_DESC $7;
BUSINESS_UNIT_DESC = "Exempt";
OUTPUT;
BUSINESS_UNIT_DESC = "General";
OUTPUT;
BUSINESS_UNIT_DESC = "Other";
OUTPUT;
RUN;
%LET _l1 = (%SYSFUNC(TRANWRD("&l1." , %STR(|) , %STR(","))));
PROC SQL;
DELETE FROM WORK.HRIR99991 WHERE BUSINESS_UNIT_DESC NOT IN &_l1.;
RUN;
In the attached I am creating a secondary macro variable which holds a transformed version of the condition you want fulfilled. Basically we are using Tranwrd to replace the | with "," and then adding the opening and closing quotation marks.
I hope this is useful.
%LET l1 = Exempt|General;
data HRIR99991;
LENGTH BUSINESS_UNIT_DESC $7;
BUSINESS_UNIT_DESC = "Exempt";
OUTPUT;
BUSINESS_UNIT_DESC = "General";
OUTPUT;
BUSINESS_UNIT_DESC = "Other";
OUTPUT;
RUN;
%LET _l1 = (%SYSFUNC(TRANWRD("&l1." , %STR(|) , %STR(","))));
PROC SQL;
DELETE FROM WORK.HRIR99991 WHERE BUSINESS_UNIT_DESC NOT IN &_l1.;
RUN;
WOW!
thanks Scott that did the trick! have a good one 🙂
Hey Scott
I would also like to display the choices made however when i do this
data _null_;
file _webout;
put '<b><h2><center><font color=red>' "&cnt1" '</font><font color=green> Records found filtering on <font color=red>' &_l1 '</font> and <font color=red>' &_l2 '</font></font></center></h2></b><br>';
run;
I get this error ...I think related to the () how can i format the &_l1 and &_l2 vars so they are PUT safe?
NOTE: Line generated by the macro variable "_L1". 1011 ("Exempt","General") ________ 22 ________ 76 ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. ERROR 76-322: Syntax error, statement will be ignored.
aaagghh crap Ignore that post i got it
proc sql noprint;
select count(*) into :cnt1 from WORK.HRIR99991;
quit;
%LET _l1display = %SYSFUNC(TRANWRD("&l1." , %STR(|) , %STR(,)));
%LET _l2display = %SYSFUNC(TRANWRD("&l2." , %STR(|) , %STR(,)));
data _null_;
file _webout;
put '<b><h2><center><font color=red>' "&cnt1" '</font><font color=green> Records found filtering on <font color=red>' &_l1display '</font> and <font color=red>' &_l2display '</font></font></center></h2></b><br>';
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.