BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chrisgo
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
5 REPLIES 5
Jagadishkatam
Amethyst | Level 16

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
hellind
Quartz | Level 8

Please try the Double Transpose Method.

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

Ksharp
Super User

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

Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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