topic Re: add extra row with missing value via sql in SAS Procedures

add extra row with missing value via sql

michtka — Thu, 07 Jun 2012 15:09:56 GMT

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,

Re: add extra row with missing value via sql

Haikuo — Thu, 07 Jun 2012 15:27:09 GMT

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

Re: add extra row with missing value via sql

michtka — Thu, 07 Jun 2012 15:38:46 GMT

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.

Re: add extra row with missing value via sql

michtka — Thu, 07 Jun 2012 15:40:27 GMT

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

Re: add extra row with missing value via sql

Haikuo — Thu, 07 Jun 2012 15:58:24 GMT

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

Re: add extra row with missing value via sql

michtka — Thu, 07 Jun 2012 16:20:06 GMT

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;