fill by group with no data

Solved
Occasional Contributor
Posts: 13

fill by group with no data

I want to populate the dates for the groups without data with 0%.

given:

 group rate date a 0.10% 1-Mar a 2.00% 2-Mar a 4.00% 3-Mar a 63.00% 4-Mar b 3.00% 1-Mar b 33.00% 2-Mar b 22.00% 3-Mar b 3.00% 4-Mar c 2.00% 1-Mar c 3.00% 2-Mar

want:

 group rate date a 0.10% 1-Mar a 2.00% 2-Mar a 4.00% 3-Mar a 63.00% 4-Mar b 3.00% 1-Mar b 33.00% 2-Mar b 22.00% 3-Mar b 3.00% 4-Mar c 2.00% 1-Mar c 3.00% 2-Mar c 0% 3-Mar c 0% 4-Mar

Thanks!

Accepted Solutions
Solution
‎04-14-2015 09:58 PM
Frequent Contributor
Posts: 93

Re: fill by group with no data

Please try the Double Transpose Method.

http://www2.sas.com/proceedings/sugi27/p016-27.pdf

All Replies
Posts: 1,147

Re: fill by group with no data

data have;

input group\$    rate\$    date\$;

cards;

a    0.10%    1-Mar

a    2.00%    2-Mar

a    4.00%    3-Mar

a    63.00%    4-Mar

b    3.00%    1-Mar

b    33.00%    2-Mar

b    22.00%    3-Mar

b    3.00%    4-Mar

c    2.00%    1-Mar

c    3.00%    2-Mar

;

proc sort data=have out=dummy(keep=date) nodupkey;

by date;

run;

proc sort data=have ;

by group date;

run;

data dummy_;

length group \$8;

set dummy;

do group='a', 'b', 'c';

output;

end;

run;

proc sort data=dummy_;

by group date;

run;

data want;

merge dummy_(in=b) have(in=a);

by group date;

if b;

if rate='' then rate='0%';

run;

proc sort data=want;

by group date;

run;

Thanks,

Jag

Thanks,
Jag
Solution
‎04-14-2015 09:58 PM
Frequent Contributor
Posts: 93

Re: fill by group with no data

Please try the Double Transpose Method.

http://www2.sas.com/proceedings/sugi27/p016-27.pdf

Super User
Posts: 10,211

Re: fill by group with no data

See a similar discussion at

You may find a path to your solution there

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,766

Re: fill by group with no data

```
data have;
input group \$    rate : percent10.    date\$;
format rate percent8.2;
cards;
a    0.10%    1-Mar
a    2.00%    2-Mar
a    4.00%    3-Mar
a    63.00%    4-Mar
b    3.00%    1-Mar
b    33.00%    2-Mar
b    22.00%    3-Mar
b    3.00%    4-Mar
c    2.00%    1-Mar
c    3.00%    2-Mar
;
run;

proc sql;
create table want as
select a.*,coalesce(b.rate,0) as rate format=percent8.2
from (select * from (select distinct group from have),(select distinct date from have)) as a
left join have as b
on a.group=b.group and a.date=b.date;
quit;

```

Xia Keshan

Posts: 1,147

Re: fill by group with no data

Alternatively,

data have;

input group\$    rate\$    date\$;

cards;

a    0.10%    1-Mar

a    2.00%    2-Mar

a    4.00%    3-Mar

a    63.00%    4-Mar

b    3.00%    1-Mar

b    33.00%    2-Mar

b    22.00%    3-Mar

b    3.00%    4-Mar

c    2.00%    1-Mar

c    3.00%    2-Mar

;

proc sort data=have;

by group descending date;

run;

data want;

set have;

output;

by     group descending date;

number=input(compress(date,,'kd'),1.);

if first.group and number<=2 then do;

group=group;rate='0%';date=put(number+1,1.)||substr(date,2);output;

group=group;rate='0%';date=put(number+2,1.)||substr(date,2);output;

end;

run;

proc sort data=want;

by group  date;

run;

Thanks,

Jag

Thanks,
Jag
🔒 This topic is solved and locked.