Help using Base SAS procedures

fill dummy with loop

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

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
Trusted Advisor
Posts: 1,231

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

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

Re: fill dummy with loop

Posted in reply to Jagadishkatam

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

Solution
‎09-03-2014 02:38 PM
Trusted Advisor
Posts: 1,231

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

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

Respected Advisor
Posts: 3,156

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,046

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 381 views
  • 6 likes
  • 5 in conversation