fill dummy with loop

[sam369]

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

[Jagadishkatam]

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

[sam369]

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

[stat_sas · Accepted solution]

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;

[sam369]

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

[Haikuo]

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

[Ksharp]

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

[sam369]

Good to know few new thing,Thank you ksharp and hai kuo.

Thanks

Sam