sas macro - subset dataset if variable contains string

[dht115]

Hello, 

I do need to subset my dataset by two different condition. 

I am getting LINE and COLUMN cannot be determined error.

/*Create dataset*/
%macro prob(var1=, var2=);
data demand&var1.problem;
	set qul.test0;
/*Need to use two seperate where condition to subset data based on var2 value*/
	if var2. contains "try1" then 
		where var2. ne 0;
	else 
		where var2. > 0;
run;
%mend;
%prob(var1=Fcst, var2=test_try1);
%prob(var1=sd, var2=test_test);

[PaigeMiller]

you have to refer to all macro variables with an ampersand

Example:

	if &var2. contains "try1" then 

[dht115]

Still I am having an error. 

I am using following logic: 

if &var2. contains "try1" then 
where var2. ne 0;
else 
where var2. > 0;

[PaigeMiller]

Please correct your code using what I said a minute ago (with emphasis)

"you have to refer to ALL macro variables with an ampersand"

[dht115]

Still no luck

[PaigeMiller]

---

@dht115 wrote:

Still no luck

---

and I can't help you further, as you didn't say what you did or what errors you have. So I have no idea what the problem is.

However, if you provide that information, then I can help.

So, for your future benefit (and also in this case), when you have code that doesn't work, you must show us the log (not the actual code) so we can see what you did and what the errors were. We need to see the ENTIRE log, every single line, every single character, with nothing removed. You need to do this from now on whenever your code isn't working, no exceptions, and don't wait until we ask to see the log.

[Tom · Accepted solution]

What are you trying to test with your IF statement?  Are you trying to test if the macro parameter VAR2 contains the string TRY1 ?  

Then you need to use macro logic to conditionally generate different WHERE statements.

%if %sysfunc(find(&var2,try1,i)) %then %do;
  where &var2. ne 0;
%end;
%else %do;
  where &var2. > 0;
%end;

If instead you need to check the values of actual data then you need to do something else. You cannot conditionally execute a WHERE statement.  The WHERE statement operates during the loading of the data, so before any IF statement could execute.  If you need to decide which observations to continue using based on the values of the actual data then use either a subsetting IF or a DELETE statement.

[Kurt_Bremser]

Step one of all macro programming: start with working non-macro code.

So please show us the non-macro code you start with.

[PaigeMiller]

@Kurt_Bremser is right (as always), and I am going to have to insist that you start by showing us working valid SAS code with no macros and no macro variables. Why? Because you don't have working SAS code, and so your macro will never work either. 

[Reeza]

1. Refer to macro variables using &
2. CONTAINS is not a valid SAS function (SQL function)
3. Because you want to introduce conditional filters, you need to use macro %IF/%THEN logic, not data step IF/THEN logic.

/*Create dataset*/
%macro prob(var1=, var2=);
data demand&var1.problem;
	set qul.test0;
/*Need to use two seperate where condition to subset data based on var2 value*/
	%if %sysfunc(find(&var2.,  "try1")>0) %then %do;
		where &var2. ne 0;
	%else
		where &var2. > 0;
run;
%mend;
%prob(var1=Fcst, var2=test_try1);
%prob(var1=sd, var2=test_test);

However your logic doesn't make sense to me. 

You're testing if VAR2 contains the value of TRY1, which implies a character variable.

But then adding a WHERE clause on VAR2 being greater than 0 or equal to 0, which implies a numeric variable. 
So this is a bit inconsistent. 

Should one of those be var1?

---

@dht115 wrote:

Hello, 

I do need to subset my dataset by two different condition. 

I am getting LINE and COLUMN cannot be determined error.

/*Create dataset*/
%macro prob(var1=, var2=);
data demand&var1.problem;
	set qul.test0;
/*Need to use two seperate where condition to subset data based on var2 value*/
	if var2. contains "try1" then 
		where var2. ne 0;
	else 
		where var2. > 0;
run;
%mend;
%prob(var1=Fcst, var2=test_try1);
%prob(var1=sd, var2=test_test);

---