Union with Where clause on each dataset

[David_Billa]

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;
      

[SASKiwi]

Try this: REPORTING_DT IS NOT MISSING

[PGStats]

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;

[David_Billa]

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

[PGStats]

How did you check that your view returns no rows?

[David_Billa]

I used create table statement to check the observation.

[PGStats]

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

[Kurt_Bremser]

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