add extra row with missing value via sql

[michtka]

Hi guys, i would like to obtain the next table:

N       TRT     col0

1        2        Number of subjects

missing       1        Number of subjects

I use proc sql to try to get this:

input subjid trt fday  tday;
    datalines;
    1  1 1 5
    2  1 . 4
    2  1 . 3
    3  1 1 4
    3  2 1 -5
    4  2 1 2
    4  1 1 4
    ;
    run;

    proc sql;
   create table total as
   select count(distinct subjid) as n, trt 'Treatment', 'number of subjects' as col0
   from new
   where fday ne . and tday le  0
   group by trt;
   quit;

    proc print data=total noobs; run;

But I obtain only one row:

N   TRT     col0

1    2          number of subjects

Not appearing the row with missing data.

Can anyone help me to write the code via sql to consider this row with missing data?

Thanks,

V

[Haikuo]

If 'n' is the result of count() , then 'n' could be '0', but it can never be missing. So what is the purpose of your code?

Haikuo

[michtka]

This is my problem, I was expected n=0 for trt=1, but  is not, because there is  not record for trt=1  with my where condition , then SAS no shows the row.

and I need to report the avobe table: trt=2 n=1 and   trt=1   n=0 , but I dont want to write hardcoding code in my code.

[michtka]

missing have to be zero in my final table,...sorry , maybe i confused you.

[Haikuo]

Then try:

proc sql;

  create table total as

  select count(fday) as n, trt 'Treatment', 'number of subjects' as col0

  from new

where fday eq . or tday le 0

  group by trt;

  quit;

Haikuo

[michtka · Accepted solution]

Thanks Hai.kuo, in terms of the table you are right, but the "where condition" cant change in my code.

I found a possible solution keeping my where condition, adding an extra condition:

     proc sql;
   create table totalx as
   select count(distinct subjid) as n, trt 'Treatment', 'number of subjects condition A' as col0
   from new
   where fday ne .
   group by trt

   union

   select count(distinct subjid) as n, trt 'Treatment', 'number of subjects condition B' as col0
   from new
   where fday ne . and tday le  0
   group by trt
   order by col0;
   quit;

   proc transpose data=totalx out=totalt (drop=_name_) prefix=trt;
   by col0;
   id trt;
   var n;
   run;