Your SAS programs, embedded in web apps and elsewhere

syntax for a stored procedure issue using proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

syntax for a stored procedure issue using proc sql

 

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;


Accepted Solutions
Solution
‎11-27-2015 09:39 AM
Super Contributor
Posts: 297

Re: syntax for a stored procedure issue using proc sql

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎11-27-2015 09:39 AM
Super Contributor
Posts: 297

Re: syntax for a stored procedure issue using proc sql

[ Edited ]

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;

Frequent Contributor
Posts: 84

Re: syntax for a stored procedure issue using proc sql

Posted in reply to Scott_Mitchell

WOW!

 

thanks Scott that did the trick! have a good one Smiley Happy

Frequent Contributor
Posts: 84

Re: syntax for a stored procedure issue using proc sql

Posted in reply to Scott_Mitchell

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.

 

Frequent Contributor
Posts: 84

Re: syntax for a stored procedure issue using proc sql

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 492 views
  • 1 like
  • 2 in conversation