Summing values based on days in separate column

[tish]

Yes. Look at the top of the code for the table add_month. Instead of defining month as "int(day / 30) + 1 as month" substitute

	case
	when day <= 30 then 1
	when day <= 60 then 2
	else .
	end as month

You will need to expand this out to set the boundaries, but pregnancies last only 10 months at the most... Remember to use a comma at the end if you will continue with another variable definition.

Hope this helps.

[Dorota_Jarosz]

Andp: Yes, you are right. I am sorry.

The function mod(Days,30) should be replaced by int(Days/30), so the data step have2 becomes:

data have2;

format id 8. Days best4.;

set have;

month=int(Days/30)+1;

if int(Days/30)=0 then Monthval1=Value;

else if int(Days/30)=1 then Monthval2=Value;

else if int(Days/30)=2 then Monthval3=Value;

else if int(Days/30)=3 then Monthval4=Value;

else if int(Days/30)=4 then Monthval5=Value;

run;

I added the month variable so it is easier to validate.

Good luck,

Dorota

[Dorota_Jarosz · Accepted solution]

For completeness I include the entire code now, together with the generated values for 10 IDs.

data have;
do Days=1 to 300;
do id=1 to 10;
Value=int(30*ranuni(1)/1);
output;
end;
end;
run;

data have2;
format id 8. Days best4.;
set have;
month=int(Days/30)+1;
if Days<=30 then month=1;
else if Days<=60 then month=2;
else if Days<=90 then month=3;
else if Days<=120 then month=4;
else if Days<=150 then month=5;
else if Days<=180 then month=6;
else if Days<=210 then month=7;
else if Days<=240 then month=8;
else if Days<=270 then month=9;
else if Days<=300 then month=10;
else month=11; *just in case *;
if month=1 then Monthval1=Value;
else if month=2 then Monthval2=Value;
else if month=3 then Monthval3=Value;
else if month=4 then Monthval4=Value;
else if month=5 then Monthval5=Value;
else if month=6 then Monthval6=Value;
else if month=7 then Monthval7=Value;
else if month=8 then Monthval8=Value;
else if month=9 then Monthval9=Value;
else if month=10 then Monthval10=Value;
else Monthval11=Value;
run;

proc sort data=have2;
by id days;
run;

%let months=Monthval1 Monthval2 Monthval3 Monthval4 Monthval5
     Monthval6 Monthval7 Monthval8 Monthval9 Monthval10;
%let sum_months=sum_Monthval1 sum_Monthval2 sum_Monthval3 sum_Monthval4 sum_Monthval5
     sum_Monthval6 sum_Monthval7 sum_Monthval8 sum_Monthval9 sum_Monthval10;
%let avg_months=avg_Monthval1 avg_Monthval2 avg_Monthval3 avg_Monthval4 avg_Monthval5
                avg_Monthval6 avg_Monthval7 avg_Monthval8 avg_Monthval9 avg_Monthval10;

proc summary data=have2  ;
var Monthval1 -- Monthval10;
by id;
output out=monthly_stats (drop=_FREQ_ _TYPE_)
  sum=&sum_months mean=&avg_months;
run;
data want;
format id best4. sum_Monthval1 best8. avg_Monthval1 8.2
sum_Monthval2 best8. avg_Monthval2 8.2
sum_Monthval3 best8. avg_Monthval3 8.2
sum_Monthval4 best8. avg_Monthval4 8.2
sum_Monthval5 best8. avg_Monthval5 8.2
sum_Monthval6 best8. avg_Monthval6 8.2
sum_Monthval7 best8. avg_Monthval7 8.2
sum_Monthval8 best8. avg_Monthval8 8.2
sum_Monthval9 best8. avg_Monthval9 8.2
sum_Monthval10 best8. avg_Monthval10 8.2;
set monthly_stats;
run;

options nocenter nodate;
proc print data=want uniform noobs;
run;

[andp]

Hi Dorota and tish,

After merging your two codes together I got it to work with my being able to change the length of each month period. The final result looks exactly how I pictured it! (I just have to create code for the trimesters but that shouldn't be too hard from here on out) Thanks again for all your help!

[Reeza]

Why not use proc means with a format?

proc format;

     value days_fmt

     0 - 30 = '<30'

      31 - 60 = '31 to 60'

      61 - 90 = '61 to 90';

run;

proc means data=have;

class id days;

format days days_fmt.;

var value;

output out=output sum= mean=/autoname;

run;

And then transpose the answer at the end if you need to, but this way you don't need to hardcode anything, ie month1, month....

[tish]

Now I understand what you want. Define month as int(day / 30) + 1 and trimester as int(day / 90) + 1. I have tested the following and it works:

proc sql;

   create table add_month as

      select

         id,

         start_date,

         end_date,

         day,

         value,

         int(day / 30) + 1 as month,

         int(day / 90) + 1 as trimester

      from

         original_data;

   create table monthly_values as

      select

         id,

         month,

         "Month" || put(month, z2.0) || "Sum" as sum_ID,

         "Month" || put(month, z2.0) || "Avg" as avg_ID,

         sum(value) as sum_value,

         avg(value) as avg_value

      from

         have

      group by

         id,

         month

      order by

         id,

         month;

quit;

proc transpose data=monthly_values out=sum_value (drop=_name_);

   id sum_ID;

   var sum_value;

   by id;

run;

proc transpose data=monthly_values out=avg_value (drop=_name_);

   id avg_ID;

   var avg_value;

   by id;

run;

data summary_data;

   merge sum_value avg_value;

   by id;

run;