syntax for a stored procedure issue using proc sql

[robm]

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;

[Scott_Mitchell · Accepted solution]

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;

[robm]

WOW!

thanks Scott that did the trick! have a good one 🙂

[robm]

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.

[robm]

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;