BookmarkSubscribeRSS Feed
Filipvdr
Pyrite | Level 9
Hello

I'm getting two stored process variables but they can look like this:

&sel_recipename=ALL&sel_recipevalue=A-STI6-200&sel_recipevalue=A-STI6-RPT1&sel_recipevalue=B-IMD-100&sel_recipevalue=B-IMD-1000&sel_recipevalue=B-IMD-1650-2&sel_recipevalue=B-IMD-200&sel_recipevalue=B-IMD-250&sel_recipevalue=B-IMD-300&sel_recipevalue=B-IMD-3000

i manage to get them like this:

RecipeNames = ALL

RecipeValues = A-STI6-200,A-STI6-RPT1,B-IMD-100,B-IMD-1000,B-IMD-1650-2,B-IMD-200,B-IMD-250,B-IMD-300,B-IMD-3000


But now, i want to use them in a where clause:
For example for RecipeValues:

%Let StringToModify = &sel_recipevalue;
%Let ModifiedString = %SysFunc(TranWrd(&StringToModify ,%Str(,), %Str(',') ) );
%Put ModifiedString = &ModifiedString;


data edc_recipe(drop=col_ins_id col_ins_row_id Facility MainTool);
set RF300l3.edc_recipe;
where facility = "&sel_facility" and Maintool ="&sel_tool"
and (RecipeValue_1 in (&sel_recipevalue) or RecipeValue_2 in (&ModifiedString) or RecipeValue_3 in (&ModifiedString) or RecipeValue_4 in (&ModifiedString))
;
run;


but this gives me no result at all, or errors
IF i use sel_recipe_value:
and RecipeName_1 in ("&sel_recipename") or RecipeName_2 in ("&sel_recipename") or RecipeName_3 in ("&sel_recipename") or RecipeName_4 in ("&sel_recipename");

NOTE: Line generated by the macro variable "SEL_RECIPEVALUE".
91 A-STI6-200,A-STI6-RPT1,B-IMD-100,B-IMD-1000,B-IMD-1650-2,B-IMD-200,B-IMD-250,B-IMD-300,B-IMD-3000
_
22
76
ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, -.

ERROR 76-322: Syntax error, statement will be ignored.

if i use my modifiedstring
No Output!! Message was edited by: Filipvdr
4 REPLIES 4
Patrick
Opal | Level 21
Hi

I believe what your macro var &sel_recipevalue in the where clause with in operator misses is some quoting.

Have a look at below example:


%let sel_recipevalue=A-STI6-200,A-STI6-RPT1,B-IMD-100,B-IMD-1000,B-IMD-1650-2,B-IMD-200,B-IMD-250,B-IMD-300,B-IMD-3000;

%put With quoting: "(%sysfunc(tranwrd(%bquote(&sel_recipevalue),%bquote(,),%bquote(","))))";

data have;
var='A-STI6-RPT1'; output;
var='xxxx xxxxxx'; output;
var='B-IMD-200'; output;
run;

data test;
set have;
where var in ("(%sysfunc(tranwrd(%bquote(&sel_recipevalue),%bquote(,),%bquote(","))))");
run;


proc print data=test;
run;


HTH
Patrick
Filipvdr
Pyrite | Level 9
mm close but not correct, unless i'm doing something wrong here

Code if RecipeValue = ALL and Recipename ne ALL:
%else %if "&sel_recipename" ne "ALL" and "&sel_recipevalue" eq "ALL" %then %do;
data edc_recipe;
set edc_recipe;
where (RecipeName_1 in ("(%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(","))))")
or RecipeName_2 in ("(%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(","))))")
or RecipeName_3 in ("(%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(","))))")
or RecipeName_4 in ("(%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(","))))")
) ;
run;
%end;

Result in my log

WHERE RecipeName_1 in ('(FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI)') or RecipeName_2 in ('(FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI)')
or RecipeName_3 in ('(FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI)') or RecipeName_4 in ('(FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI)');

the comma is placed before the bracket?
Patrick
Opal | Level 21
Hi

Yes, you're definitely doing something wrong here.

First of all it looks to me as if the code posted is not the one which generated the log (double quotes used in the code but single quotes generated in the log??).

Second: If you look at the log you'll see that the outer quotes are outside of the brackets. It seems that the original string you're using starts and ends with brackets - that's not like the example you initially posted.

Try and get rid of the brackets - either by not populating the macro var with brackets in first place or by removing the brackets by using compres().

Solving the details is now up to you. I think you've got the idea 😉

Cheers
Patrick
Filipvdr
Pyrite | Level 9
Yes yes it works now, you set me up the right way, so thanks for that. Solution was not hard anymore.

%if "&sel_recipename" ne "ALL" and "&sel_recipevalue" ne "ALL" %then %do;
data edc_recipe;
set edc_recipe;
where ((RecipeName_1 in ("%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(",")))")
or RecipeName_2 in ("%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(",")))")
or RecipeName_3 in ("%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(",")))")
or RecipeName_4 in ("%sysfunc(tranwrd(%bquote(&sel_recipename),%bquote(,),%bquote(",")))")
) AND (
RecipeValue_1 in ("%sysfunc(tranwrd(%bquote(&sel_recipevalue),%bquote(,),%bquote(",")))")
or RecipeValue_2 in ("%sysfunc(tranwrd(%bquote(&sel_recipevalue),%bquote(,),%bquote(",")))")
or RecipeValue_3 in ("%sysfunc(tranwrd(%bquote(&sel_recipevalue),%bquote(,),%bquote(",")))")
or RecipeValue_4 in ("%sysfunc(tranwrd(%bquote(&sel_recipevalue),%bquote(,),%bquote(",")))")
))
;
run;
%end;

gives where statement:

WHERE (RecipeName_1 in ('FcRcpaSEQUENCE_AMAT_CVD_B_C', 'FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI') or RecipeName_2 in ('FcRcpaSEQUENCE_AMAT_CVD_B_C',
'FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI') or RecipeName_3 in ('FcRcpaSEQUENCE_AMAT_CVD_B_C', 'FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A
STI') or RecipeName_4 in ('FcRcpaSEQUENCE_AMAT_CVD_B_C', 'FcRcpaSEQUENCE_AMAT_CVD_DXZ_C', 'FcRcpaSEQUENCE_AMAT_CVD_HDP_A STI')) and (RecipeValue_1 in ('BC-IMD25-A1', 'C-ARC-RPT1',
'C-ARCN2O-RPT', 'C-BLOK-200') or RecipeValue_2 in ('BC-IMD25-A1', 'C-ARC-RPT1', 'C-ARCN2O-RPT', 'C-BLOK-200') or RecipeValue_3 in ('BC-IMD25-A1', 'C-ARC-RPT1', 'C-ARCN2O-RPT',
'C-BLOK-200') or RecipeValue_4 in ('BC-IMD25-A1', 'C-ARC-RPT1', 'C-ARCN2O-RPT', 'C-BLOK-200'));

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 845 views
  • 0 likes
  • 2 in conversation