IF column exist then apply where condition

[Ronein]

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;

[yabwon]

memname = "HAVE" ?

[yabwon]

and:

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

[Ronein]

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

[Ronein]

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

[yabwon]

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

[yabwon]

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

[Ronein]

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;

[Tom]

---

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

[Kurt_Bremser]

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;

[Ronein]

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

[Kurt_Bremser]

---

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