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;
Try this: REPORTING_DT IS NOT MISSING
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;
How did you check that your view returns no rows?
It may be that you tried to create a table AND a view with the same name? SAS doesn't allow that.
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:
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.