topic Re: IF column exist then apply where condition in SAS Programming

IF column exist then apply where condition

Ronein — Tue, 04 Apr 2023 08:55:56 GMT

Hello

In the following code user define a column name and value to select rows.

If the user define a column that doesn't exist in data set then the want data set should contain all rows.

If the user define a column that exist in data set then the want data set should contain rows after using where condition.

In "case1" of my code I expect to get all rows and I get 0 rows in want data set- why??

May you help to correct the code

data have;
input X y z W $;
cards;
10 20 30 A
40 25 38 B
20 40 80 C
;
Run;

/****Case1****/
/****Case1****/
/****Case1****/
%let User_Col_name = Q;
%let Value_Select =10;


proc sql noprint;
select count(*) into :IndExist_User_Col
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and upcase(name) = upcase("&User_Col_name.");
quit;
%put &IndExist_User_Col;

data want;
set have;
if &IndExist_User_Col=1 then do;
IF &User_Col_name.=&Value_Select. then output;
end;
run;
/**Problem!!!I expect to see all rows***/

/****Case2****/
/****Case2****/
/****Case2****/
%let User_Col_name = X;
%let Value_Select =10;


proc sql noprint;
select count(*) into :IndExist_User_Col
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and upcase(name) = upcase("&User_Col_name.");
quit;
%put &IndExist_User_Col;

data want;
set have;
if &IndExist_User_Col=1 then do;
IF &User_Col_name.=&Value_Select. then output;
end;
run;

Re: IF column exist then apply where condition

yabwon — Tue, 04 Apr 2023 08:26:08 GMT

memname = "HAVE" ?

Re: IF column exist then apply where condition

yabwon — Tue, 04 Apr 2023 08:30:40 GMT

and:

if &IndExist_User_Col=1 then do;
IF &User_Col_name.=&Value_Select. then output;
end;
else output;

Re: IF column exist then apply where condition

yabwon — Tue, 04 Apr 2023 08:45:58 GMT

you can also consider macro %if-statement:

data want;
set have;
  %if &IndExist_User_Col=1 %then %do;
    where &User_Col_name.=&Value_Select.;
  %end;
output;
run;

Bart

Re: IF column exist then apply where condition

Ronein — Tue, 04 Apr 2023 08:56:59 GMT

Yes, sorry ,by mistake it was written ttt but I fixed it

Re: IF column exist then apply where condition

yabwon — Tue, 04 Apr 2023 09:17:10 GMT

I played a bit with datastep searching 🙂

options fullstimer;
%macro FilterData(
 have = SASHELP.CLASS
,want = WORK.WANT
,User_Col_name = AGE
,Value_Select = 14
);

%local IndExist_User_Col; %let IndExist_User_Col=0;
data _null_;
  if 0 then set &have.;
  length __name $ 32 __type $ 1 __length 8;
  do _N_ = 1 by 1 until(^__length or __name='__name');
    call vnext(__name, __type, __length);
    /*put _all_;*/
    if upcase(__name) = %upcase("&User_Col_name.") and __type = "N" then
      do;
        call symputx("IndExist_User_Col", 1, "L");
        stop;
      end;     
  end;
  stop;
run;
%put &=IndExist_User_Col;

data &want.;
  set &have.;
  %if &IndExist_User_Col=1 %then %do;
    where &User_Col_name.=&Value_Select.;
  %end;
  output;
run;
%mend FilterData;

%FilterData();

%FilterData(User_Col_name = NAME);

[EDIT] I added `%local IndExist_User_Col;` to macro.

Bart

Re: IF column exist then apply where condition

Kurt_Bremser — Tue, 04 Apr 2023 09:39:19 GMT

Start a new, pristine SAS session.

Then run this (only this) and read the log:

data have;
input X y z W $;
cards;
10 20 30 A
40 25 38 B
20 40 80 C
;
Run;

/****Case1****/
/****Case1****/
/****Case1****/
%let User_Col_name = Q;
%let Value_Select =10;

proc sql noprint;
select count(*) into :IndExist_User_Col
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and upcase(name) = upcase("&User_Col_name.");
quit;
%put &IndExist_User_Col;

Re: IF column exist then apply where condition

Ronein — Tue, 04 Apr 2023 21:19:25 GMT

Thanks,
Why is it needed to add the sentence of else ouput?

Re: IF column exist then apply where condition

Ronein — Tue, 04 Apr 2023 21:20:18 GMT

Thanks
May you please explain why my code didnt work?
if &IndExist_User_Col=1 then do;
IF &User_Col_name.=&Value_Select. then output;
end;

Re: IF column exist then apply where condition

Ronein — Tue, 04 Apr 2023 21:24:08 GMT

What do you mean "pristine sas session".
This cose work 100% but it is only part of the code i run

Re: IF column exist then apply where condition

Tom — Tue, 04 Apr 2023 23:33:02 GMT

@Ronein wrote:
Thanks
May you please explain why my code didnt work?
if &IndExist_User_Col=1 then do;
IF &User_Col_name.=&Value_Select. then output;
end;

Adding the ELSE will cause it to output ALL of the observations when the first condition is FALSE.

If you run a normal data step that does not include any OUTPUT statements then SAS will add "implied output" at the end of the step. But once you have any OUTPUT statements it will not do the implied output.

So your code works fine when the first IF condition is TRUE. It then runs the second if and conditionally writes the observation.

But when the first IF is FALSE then it never executes any OUTPUT statements. So you always get zero observations written.

You could also redo the logic to avoid the use of the OUTPUT statement.

if &IndExist_User_Col=1 then do;
  IF &User_Col_name. NE &Value_Select. then DELETE;
end;

Re: IF column exist then apply where condition

Kurt_Bremser — Wed, 05 Apr 2023 04:46:54 GMT

@Ronein wrote:
What do you mean "pristine sas session".
This cose work 100% but it is only part of the code i run

Pristine = unsullied, unmodified

Which means (for a SAS session) no previous code of yours has already set macro variables or altered anything from the startup state.

Your first SQL will not find any observations because of the WHERE clause, so the macro variable is not created.