Can you show an example of the output dataset you want to create? You're using the word MERGE, but that has a specific meaning in SAS, which I don't think is what you intent.
It sounds like you want to vertically concatenate / stack the four datasets, and get an output dataset where you have 21 records, which are 10 records from males, 9 records from females, and two records with all missing values, from no trans and no other. Is that right?
In SAS, a 0 obs dataset has no rows. This is different from having one row with missing values.
If you want to create a dataset with one row of missing values when a WHERE condition is false, you could do it like:
data foo;
if last then output ;
set sashelp.class end=last;
where sex="Other";
run;
During compile time, the compiler will set the variable LAST to the value 1 if there are no records that meet the WHERE statement. (Side note: the documentation claims the end= variable is always initialized to zero and will not be set to 1 until the SET statement executes, which is incorrect.)
Above will output one record, but it will also only output one record if you change the where clause to sex="M". So you can make the output statement conditional to only output a record with missing values when on the first iteration of the data step, before the SET statement has executed, LAST=1. And you have to add another OUTPUT statement at the bottom of the loop, which will output records that do meet the WHERE clause.
So you could your four steps like:
data ob1;
if _N_=1 and last then output ;
set sashelp.class end=last;
where sex="M";
output ;
run;
data ob2;
if _N_=1 and last then output ;
set sashelp.class end=last;
where sex="F";
output ;
run;
data ob3;
if _N_=1 and last then output ;
set sashelp.class end=last;
where sex="Trans";
output ;
run;
data ob4;
if _N_=1 and last then output ;
set sashelp.class end=last;
where sex="Other";
output ;
run;
Then concatenation them:
data want ;
set ob1-ob4 ;
run ;
Result is:
Obs Name Sex Age Height Weight
1 Alfred M 14 69.0 112.5
2 Henry M 14 63.5 102.5
3 James M 12 57.3 83.0
4 Jeffrey M 13 62.5 84.0
5 John M 12 59.0 99.5
6 Philip M 16 72.0 150.0
7 Robert M 12 64.8 128.0
8 Ronald M 15 67.0 133.0
9 Thomas M 11 57.5 85.0
10 William M 15 66.5 112.0
11 Alice F 13 56.5 84.0
12 Barbara F 13 65.3 98.0
13 Carol F 14 62.8 102.5
14 Jane F 12 59.8 84.5
15 Janet F 15 62.5 112.5
16 Joyce F 11 51.3 50.5
17 Judy F 14 64.3 90.0
18 Louise F 12 56.3 77.0
19 Mary F 15 66.5 112.0
20 . . .
21 . . .
If you want Sex to have the value "T" or "O", you could add an assignment statement to the top of the DATA step.
There are probably easier ways to make a row of missing values. For example, you could make a dataset with four records with values of sex "M" "F" "T" "O" and merge/join that against sashelp.class, and that would do it:
data grid ;
do sex="M","F","T","O" ;
output ;
end ;
run ;
proc sql ;
create table want2 as
select a.sex as sex_grid, b.* from
grid as a left join sashelp.class as b
on a.sex=b.sex
;
quit ;
... View more
