Help using Base SAS procedures

fill by group with no data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

fill by group with no data

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

given:

groupratedate
a0.10%1-Mar
a2.00%2-Mar
a4.00%3-Mar
a63.00%4-Mar
b3.00%1-Mar
b33.00%2-Mar
b22.00%3-Mar
b3.00%4-Mar
c2.00%1-Mar
c3.00%2-Mar

want:

groupratedate
a0.10%1-Mar
a2.00%2-Mar
a4.00%3-Mar
a63.00%4-Mar
b3.00%1-Mar
b33.00%2-Mar
b22.00%3-Mar
b3.00%4-Mar
c2.00%1-Mar
c3.00%2-Mar
c0%3-Mar
c0%4-Mar

Thanks!


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

Re: fill by group with no data


All Replies
Trusted Advisor
Posts: 1,131

Re: fill by group with no data

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

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

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: 6,972

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
Super User
Posts: 9,691

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

Trusted Advisor
Posts: 1,131

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 286 views
  • 6 likes
  • 5 in conversation