topic Re: fill dummy with loop in SAS Procedures

fill dummy with loop

sam369 — Wed, 03 Sep 2014 17:51:33 GMT

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

Re: fill dummy with loop

Jagadishkatam — Wed, 03 Sep 2014 18:13:02 GMT

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;

run;

data final;

set have want;

run;

Thanks,

Jag

Re: fill dummy with loop

sam369 — Wed, 03 Sep 2014 18:23:04 GMT

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

Re: fill dummy with loop

stat_sas — Wed, 03 Sep 2014 18:38:56 GMT

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;

Re: fill dummy with loop

sam369 — Wed, 03 Sep 2014 18:58:29 GMT

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

Re: fill dummy with loop

Haikuo — Wed, 03 Sep 2014 19:49:35 GMT

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;

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

Re: fill dummy with loop

Ksharp — Thu, 04 Sep 2014 13:04:20 GMT

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

Re: fill dummy with loop

sam369 — Thu, 04 Sep 2014 15:28:27 GMT

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

Thanks

Sam