BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robm
Quartz | Level 8

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Scott_Mitchell
Quartz | Level 8

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

4 REPLIES 4
Scott_Mitchell
Quartz | Level 8

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
Quartz | Level 8

WOW!

 

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

robm
Quartz | Level 8

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
Quartz | Level 8

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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