DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 272

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

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;

 

PROC Star
Posts: 553

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

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?

Super Contributor
Posts: 272

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

Then my output is

 

NS TRT

2    30mg

3    45mg 

1     placebo

1     15mg

 

Thank you

PROC Star
Posts: 63

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

I would probably try something like

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

 

Ask a Question
Discussion stats
  • 3 replies
  • 142 views
  • 0 likes
  • 3 in conversation