proc sql total

[michtka]

Hi everyone, there are anotehr way in proc sql to obtain the final value,

I've had to use another datasset new to get it, but i dunno if i can do it in the proc sql queries...thanks.

data have;

   length leg  sid 8  phase  treattxt $10;

   input sid leg phase $ treattxt $;

   datalines;

   1 100 active drug

   1 100 off    drug

   2 101 active placebo

   3 101 off    placebo

   3 101 active placebo

   4 100 active drug

   5 100 active drug

   6 101 active placebo

   6 101 off    placebo

   ;

   run;

proc sql noprint;
     create table ntreat as
     select count(distinct sid) as n, leg, treattxt
   from have
   where phase='active'
   group leg, treattxt;
   create table ntotal as
    select count(distinct sid) as n, ' total' as treattxt,9999 as leg
   from have
   where phase='active';
   quit;

    data new;
      set ntreat ntotal;
      col0= ' ';
    run;

[LinusH]

Looks like you want to store a report layout in a table?

It's better to do the total in the report step, whatever how you so that?

SQL it's not suited to do tailored reports, therefore these three steps of yours.

[michtka]

nop, i only wanted avoid the last dataset, and try to calculate the total value in the same query, that's all.

[art297]

What are you trying to get the total of?  Its not clear from your post.

[michtka]

I just created and extratable called ntotal, to calculate the total subjects, and i dunno if I can calculate it, in the first query (table ntreat)

thanks.

create table ntotal as

    select count(distinct sid) as n, ' total' as treattxt,9999 as leg

   from have

   where phase='active';

[LinusH]

Again, storing totals is usually not best practice, it's a report thing.

But if you still think this is a good idea, just use insert into instead of create table.

[art297]

proc sql noprint;

  create table new as

    select count(distinct sid) as n, leg, treattxt,' ' as col0

      from have

        where phase='active'

          group leg, treattxt

  ;

  insert into new

    select count(distinct sid) as n, 9999 as leg,'total' as treattxt length=10,' ' as col0

      from have

        where phase='active'

  ;

quit;

[Reeza]

I don't believe any of the standard SAS procs do a COUNT DISTINCT so you actually do need to store your total in a dataset in this case .

[michtka]

Sorry, what do you mean, can explain it better? thnaks.

[art297]

I think she was simply agreeing with you that doing it in proc sql was a good approach.

[Ksharp]

You can use SQL's vertical union operator.

proc sql noprint;

     create table want as

     select count(distinct sid) as n, leg, treattxt

   from have

   where phase='active'

   group leg, treattxt;

union all corr

    select count(distinct sid) as n, ' total' as treattxt,9999 as leg

   from have

   where phase='active';

   quit;

if you want COL0 be involved .use

outer union corr

Ksharp

[NagendraKumarK]

Hi,

Can you try this below?

proc sql noprint;

create table new as
(select distinct put(leg,3.) as leg,treattxt, count(distinct sid) as cnt from have group by leg,treattxt having phase='active'
  union all
  select  '',"total", count(distinct sid) from have where phase='active'

);

   quit;