## fill dummy with loop

Solved
Regular Contributor
Posts: 171

# fill dummy with loop

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

Accepted Solutions
Solution
‎09-03-2014 02:38 PM
Posts: 1,270

## Re: fill dummy with loop

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;

All Replies
Posts: 1,147

## Re: fill dummy with loop

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

Thanks,
Jag
Regular Contributor
Posts: 171

## Re: fill dummy with loop

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

Solution
‎09-03-2014 02:38 PM
Posts: 1,270

## Re: fill dummy with loop

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;

Regular Contributor
Posts: 171

## Re: fill dummy with loop

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

Posts: 3,167

## Re: fill dummy with loop

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

Super User
Posts: 10,787

## Re: fill dummy with loop

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

Regular Contributor
Posts: 171

## Re: fill dummy with loop

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

Thanks

Sam

🔒 This topic is solved and locked.