DATA Step, Macro, Functions and more

Use Stored Process Variables

Reply
Regular Contributor
Posts: 229

Use Stored Process Variables

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
Respected Advisor
Posts: 3,887

Re: Use Stored Process Variables

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
Regular Contributor
Posts: 229

Re: Use Stored Process Variables

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?
Respected Advisor
Posts: 3,887

Re: Use Stored Process Variables

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
Regular Contributor
Posts: 229

Re: Use Stored Process Variables

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'));
Ask a Question
Discussion stats
  • 4 replies
  • 161 views
  • 0 likes
  • 2 in conversation