topic Re: sas macro - subset dataset if variable contains string in SAS Programming

sas macro - subset dataset if variable contains string

dht115 — Wed, 22 Dec 2021 14:55:59 GMT

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);

Re: sas macro - subset dataset if variable contains string

PaigeMiller — Wed, 22 Dec 2021 15:04:42 GMT

you have to refer to all macro variables with an ampersand

Example:

	if &var2. contains "try1" then

Re: sas macro - subset dataset if variable contains string

dht115 — Wed, 22 Dec 2021 15:09:35 GMT

Still I am having an error.

I am using following logic:

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

Re: sas macro - subset dataset if variable contains string

PaigeMiller — Wed, 22 Dec 2021 15:14:04 GMT

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

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

Re: sas macro - subset dataset if variable contains string

dht115 — Wed, 22 Dec 2021 15:17:59 GMT

Still no luck

Re: sas macro - subset dataset if variable contains string

Tom — Wed, 22 Dec 2021 15:19:00 GMT

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.

Re: sas macro - subset dataset if variable contains string

PaigeMiller — Wed, 22 Dec 2021 15:20:49 GMT

@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.

Re: sas macro - subset dataset if variable contains string

Kurt_Bremser — Wed, 22 Dec 2021 15:21:34 GMT

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

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

Re: sas macro - subset dataset if variable contains string

PaigeMiller — Wed, 22 Dec 2021 15:31:20 GMT

@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.

Re: sas macro - subset dataset if variable contains string

Reeza — Wed, 22 Dec 2021 16:18:31 GMT

Refer to macro variables using &
CONTAINS is not a valid SAS function (SQL function)
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);