topic Re: proc sql total in SAS Procedures

proc sql total

michtka — Tue, 20 Nov 2012 10:14:37 GMT

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;

Re: proc sql total

LinusH — Tue, 20 Nov 2012 12:32:36 GMT

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.

Re: proc sql total

michtka — Tue, 20 Nov 2012 13:23:47 GMT

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

Re: proc sql total

art297 — Tue, 20 Nov 2012 13:24:08 GMT

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

Re: proc sql total

michtka — Tue, 20 Nov 2012 13:41:48 GMT

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

Re: proc sql total

LinusH — Tue, 20 Nov 2012 14:11:26 GMT

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.

Re: proc sql total

art297 — Tue, 20 Nov 2012 14:21:17 GMT

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;

Re: proc sql total

Reeza — Tue, 20 Nov 2012 15:34:55 GMT

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 .

Re: proc sql total

michtka — Tue, 20 Nov 2012 18:55:08 GMT

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

Re: proc sql total

art297 — Tue, 20 Nov 2012 19:35:46 GMT

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

Re: proc sql total

Ksharp — Wed, 21 Nov 2012 03:17:37 GMT

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

Re: proc sql total

NagendraKumarK — Wed, 16 Jan 2013 16:54:54 GMT

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;