topic Re: fill by group with no data in SAS Procedures

fill by group with no data

chrisgo — Wed, 15 Apr 2015 01:02:26 GMT

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!

Re: fill by group with no data

Jagadishkatam — Wed, 15 Apr 2015 01:47:29 GMT

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

Re: fill by group with no data

hellind — Wed, 15 Apr 2015 01:58:31 GMT

Please try the Double Transpose Method.

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

Re: fill by group with no data

Kurt_Bremser — Wed, 15 Apr 2015 05:43:43 GMT

See a similar discussion at

You may find a path to your solution there

Re: fill by group with no data

Ksharp — Wed, 15 Apr 2015 13:11:00 GMT


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

Re: fill by group with no data

Jagadishkatam — Wed, 15 Apr 2015 15:31:08 GMT

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