topic Re: syntax for a stored procedure issue using proc sql in Developers

syntax for a stored procedure issue using proc sql

robm — Thu, 26 Nov 2015 20:41:27 GMT

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;

Re: syntax for a stored procedure issue using proc sql

Scott_Mitchell — Fri, 27 Nov 2015 03:20:27 GMT

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;

Re: syntax for a stored procedure issue using proc sql

robm — Fri, 27 Nov 2015 14:40:04 GMT

WOW!

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

Re: syntax for a stored procedure issue using proc sql

robm — Fri, 27 Nov 2015 17:36:58 GMT

Hey Scott

I would also like to display the choices made however when i do this

data _null_;
file _webout;
put '<h2><center>' "&cnt1" ' Records found filtering on ' &_l1 ' and ' &_l2 '</center></h2> ';
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.

Re: syntax for a stored procedure issue using proc sql

robm — Fri, 27 Nov 2015 17:42:35 GMT

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 '<h2><center>' "&cnt1" ' Records found filtering on ' &_l1display ' and ' &_l2display '</center></h2> ';
run;