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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.