Hi All,
i have a data with missing trt. i need to fill with dummy
for EX:
trt code cnt ll ul
1 abc 2 4 5
1 bbc 2 4 5
1 cbc 2 4 5
2 abc 2 4 5
2 bbc 2 4 5
2 cbc 2 4 5
4 abc 2 4 5
4 bbc 2 4 5
4 cbc 2 4 5
6 abc 2 4 5
6 bbc 2 4 5
6 cbc 2 4 5
in the above example 3 and 5 is missing i need to trt with missing value and cnt ,ll,ul will be with 0
want:
trt code cnt ll ul
1 abc 2 4 5
1 bbc 2 4 5
1 cbc 2 4 5
2 abc 2 4 5
2 bbc 2 4 5
2 cbc 2 4 5
3 abc 0 0 0
3 bbc 0 0 0
3 cbc 0 0 0
4 abc 2 4 5
4 bbc 2 4 5
4 cbc 2 4 5
5 abc 0 0 0
5 bbc 0 0 0
5 cbc 0 0 0
6 abc 2 4 5
6 bbc 2 4 5
6 cbc 2 4 5
Thanks
Sam
data base;
do trt = 1 to 6;
do code='abc','bbc','cbc';
output;
end;
end;
run;
proc sql;
select distinct base.trt, base.code,coalesce(cnt,0) as cnt, coalesce(ll,0) as ll, coalesce(ul,0) as ul
from base left join
have on base.trt=have.trt;
quit;
consider that have is your original dataset and want is the dummy dataset
data want;
do trt = 3,5;
do code='abc','bbc','cbc';
output;
end;
end;
run;
data final;
set have want;
run;
Thanks,
Jag
Thank you jagadish
for time being i didt the same approach , like creating the dummy dataset and concatenate with original dataset.. but not all the time the same trt is missing(in this case 3 and 5) some time another trt(like 1,2,3,4,5). so i need to do in dynamic way instead of creating dummy dataset.
i am trying with do until(last.trt) but unsucessful yet
Thanks
Sam
data base;
do trt = 1 to 6;
do code='abc','bbc','cbc';
output;
end;
end;
run;
proc sql;
select distinct base.trt, base.code,coalesce(cnt,0) as cnt, coalesce(ll,0) as ll, coalesce(ul,0) as ul
from base left join
have on base.trt=have.trt;
quit;
Thank you stat!!!Exactly That is what ,what i want, i am still looking for some one to give the answer in datastep .
Thanks
Sam
OK, data step as you asked. Some of the code was borrowed from @stat@sas:
data base;
do trt = 1 to 6;
do code='abc','bbc','cbc';
cnt=0;
ll=0;
ul=0;
output;
end;
end;
run;
data have;
input trt code :$3. cnt ll ul;
cards;
1 abc 2 4 5
1 bbc 2 4 5
1 cbc 2 4 5
2 abc 2 4 5
2 bbc 2 4 5
2 cbc 2 4 5
4 abc 2 4 5
4 bbc 2 4 5
4 cbc 2 4 5
6 abc 2 4 5
6 bbc 2 4 5
6 cbc 2 4 5
;
data want;
update base have;
by trt code;
run;
Haikuo
I am too lazy to type more code.
data have; input trt code :$3. cnt ll ul; cards; 1 abc 2 4 5 1 bbc 2 4 5 1 cbc 2 4 5 2 abc 2 4 5 2 bbc 2 4 5 2 cbc 2 4 5 4 abc 2 4 5 4 bbc 2 4 5 4 cbc 2 4 5 6 abc 2 4 5 6 bbc 2 4 5 6 cbc 2 4 5 ; run; proc sql noprint; select min(trt),max(trt) into : min,: max from have; quit; data trt; do trt=&min to &max;output;end; run; proc sql; create table want as select * from (select * from (select distinct trt from trt),(select distinct code from have)) natural left join have ; quit; proc stdize data=want out=want reponly missing=0;run;
Xia Keshan
Good to know few new thing,Thank you ksharp and hai kuo.
Thanks
Sam
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.