topic Re: considering a row with missing data via sql in SAS Procedures

considering a row with missing data via sql

michtka — Thu, 07 Jun 2012 14:51:11 GMT

x
Hi guys, I would like to obtain the next table total:

N TRT col0

1 2 Number of subjects

. 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,

Re: considering a row with missing data via sql

Ksharp — Fri, 08 Jun 2012 02:01:26 GMT

You can't select 1 anymore because your WHERE condition has already exclude it. But you can get it by your hand.

data new;
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
    union 
   select .,1,'number of subjects' from new(obs=1)
;
   quit;

Ksharp

Re: considering a row with missing data via sql

ChrisNZ — Tue, 12 Jun 2012 02:35:59 GMT

Only one row is read from your table.

Only row

3 2 1 -5

matches

where fday ne . and tday le 0