Help using Base SAS procedures

add extra row with missing value via sql

Accepted Solution Solved
Reply
Super Contributor
Posts: 301
Accepted Solution

add extra row with missing value via sql

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



Accepted Solutions
Solution
‎06-07-2012 12:20 PM
Super Contributor
Posts: 301

Re: add extra row with missing value via sql

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: add extra row with missing value via sql

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

Super Contributor
Posts: 301

Re: add extra row with missing value via sql

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.


Super Contributor
Posts: 301

Re: add extra row with missing value via sql

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

Respected Advisor
Posts: 3,124

Re: add extra row with missing value via sql

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

Solution
‎06-07-2012 12:20 PM
Super Contributor
Posts: 301

Re: add extra row with missing value via sql

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 452 views
  • 0 likes
  • 2 in conversation