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!
Please try the Double Transpose Method.
Please try
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
Please try the Double Transpose Method.
See a similar discussion at
You may find a path to your solution there
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.