topic Re: Union with Where clause on each dataset in SAS Programming

Union with Where clause on each dataset

David_Billa — Mon, 31 Aug 2020 02:07:00 GMT

I'm trying to Union the three datasets and in the final target dataset,I want to display all the records where REPORTING_DT variable values are not missing.It's a numeric variable and it has both missing and non missing values.

When I tried the code below, I could see no record is being created in the ouput.I did tried with 'Where REPORTING_DT <> .' but still it's not producing any rows in the Output.

Code which I used is,

proc sql;
   create view work.TMP_Final_Output as
      select
      Have1.UNIT,
      Have1.REPORTING_CAUSE,
      Have1.REPORTING_DT      
   from
      Have1
   where
      REPORTING_DT NOT IS MISSING 
   union all  
   select
      Have2.UNIT,
      Have2.REPORTING_CAUSE,
      Have2.REPORTING_DT
         from
      Have2
   where
      REPORTING_DT NOT IS MISSING 
   union all  
   select
      Have3.UNIT,
      Have3.REPORTING_CAUSE,
      Have3.REPORTING_DT
      from
      Have3
   where
      REPORTING_DT NOT IS MISSING ;
quit;

Re: Union with Where clause on each dataset

SASKiwi — Mon, 31 Aug 2020 02:10:35 GMT

Try this: REPORTING_DT IS NOT MISSING

Re: Union with Where clause on each dataset

PGStats — Mon, 31 Aug 2020 02:40:56 GMT

But this works...

data a;
set sashelp.class;
where age =12;
run;

data b;
set sashelp.class;
where age =13;
run;

data c;
set sashelp.class;
where age =14;
run;

proc sql;
create view d as
select name from a where name not is missing
union all
select name from b where name not is missing
union all
select name from c where name not is missing;
select * from d;
quit;

Re: Union with Where clause on each dataset

PGStats — Mon, 31 Aug 2020 02:41:52 GMT

How did you check that your view returns no rows?

Re: Union with Where clause on each dataset

David_Billa — Mon, 31 Aug 2020 02:51:51 GMT

I used create table statement to check the observation.

Re: Union with Where clause on each dataset

David_Billa — Mon, 31 Aug 2020 02:55:51 GMT

It's not working as I don't see any records in the output.

Re: Union with Where clause on each dataset

PGStats — Mon, 31 Aug 2020 03:08:42 GMT

It may be that you tried to create a table AND a view with the same name? SAS doesn't allow that.

Re: Union with Where clause on each dataset

Kurt_Bremser — Mon, 31 Aug 2020 05:34:29 GMT

You only create a view. A view does not do anything before it is used.

If that was only part of your code, then please:

post usable example data for have1, have2, have3 (dara steps with datalines
and your whole code that led to the "zero" result.
and the log from that code