BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sam369
Obsidian | Level 7


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

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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

7 REPLIES 7
Jagadishkatam
Amethyst | Level 16

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
sam369
Obsidian | Level 7

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
Ammonite | Level 13

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
Obsidian | Level 7

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
Onyx | Level 15

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
Super User

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
Obsidian | Level 7

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

Thanks

Sam

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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