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: 768

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

Posted in reply to knveraraju91

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: 102

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

Posted in reply to knveraraju91

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
  • 145 views
  • 0 likes
  • 3 in conversation