Help using Base SAS procedures

proc sql total

Reply
Super Contributor
Posts: 301

proc sql total

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;

Super User
Posts: 5,260

Re: proc sql total

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.

Data never sleeps
Super Contributor
Posts: 301

Re: proc sql total

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

PROC Star
Posts: 7,366

Re: proc sql total

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

Super Contributor
Posts: 301

Re: proc sql total

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';

Super User
Posts: 5,260

Re: proc sql total

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.

Data never sleeps
PROC Star
Posts: 7,366

Re: proc sql total

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;

Super User
Posts: 17,958

Re: proc sql total

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 Smiley Happy.

Super Contributor
Posts: 301

Re: proc sql total

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

PROC Star
Posts: 7,366

Re: proc sql total

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

Super User
Posts: 9,691

Re: proc sql total

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

Occasional Contributor
Posts: 14

Re: proc sql total

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;


  

Ask a Question
Discussion stats
  • 11 replies
  • 429 views
  • 0 likes
  • 6 in conversation