topic Re: How to get count as zero when there are no subjects in the data in SAS Programming

How to get count as zero when there are no subjects in the data

knveraraju91 — Thu, 25 May 2017 21:59:16 GMT

Dear,

In the ouput result of the below program (in the grey box), I need to have zero count(NS=0) when there are no subjects present for the treatment.

Output needed:

NS TRT

2 30mg

3 45mg

1 placebo

0 15mg

output getting

NS TRT

2 30mg

3 45mg

1 placebo

data one;
input id trt$ sflag$;
datalines;
1 45mg Y 
2 30mg Y
3 15mg N
4 30mg Y
5 45mg Y
6 plcebo Y
7 45mg Y
;
PROC SQL;
create table two as 
select count(distinct id) as NS,trt
from one
where sflag='Y'
group by trt;
quit;

I think I can get the output by two proc transpose and two datsteps inbetween. But please suggest me if any better way to do this. Thank you very much

program I am running to get output I need

proc transpose data=two out=three(drop=_NAME_);
var NS;
id TRT;
run;

data four;
set three;
array data _15mg _30mg _45mg placebo;
do over data;
if missing(data) then data=0;
end;
TOTAL=_15mg + _30mg + _45mg ;
run;

proc transpose data=four out=five;
var placebo _15mg _30mg _45mg total ;
run;

Re: How to get count as zero when there are no subjects in the data

PeterClemmensen — Thu, 25 May 2017 22:12:05 GMT

It can be done simpler. But what if your data looked like this?

data one;
input id trt$ sflag$;
datalines;
1 45mg Y 
2 30mg Y
3 15mg N
4 30mg Y
5 45mg Y
6 plcebo Y
7 45mg Y
8 15mg Y
;

So that you also had a 15mg observation with sflag='Y'. What would your desired output look like then?

Re: How to get count as zero when there are no subjects in the data

knveraraju91 — Thu, 25 May 2017 22:23:22 GMT

Then my output is

NS TRT

2 30mg

3 45mg

1 placebo

1 15mg

Thank you

Re: How to get count as zero when there are no subjects in the data

s_lassen — Sun, 28 May 2017 17:01:26 GMT

I would probably try something like

proc sql;
  create table want as
  select trt,sum(sflag='Y')
  from have
  group by trt;
quit;