I did not check this code... I gave you two solutions for the month calculation, one the calendar month (commented out) and then another which should give you 30 day months. Also, I dropped the variable _name_ from the output datasets only to simplify the merge. Me, I'd leave it in for a while to double check the transposition. I'm not a transposing whiz, so there might be a way to optimize here, too. proc sql; create table add_month as select id, end_date, /* intck("month", "31Dec1999"d, end_date) as month, */ int((end_date - "01Jan2000"d) / 30) + 1 as month, value, days 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 add_month group by 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 desired_data; merge sum_value avg_value; by id; run; Hope this helps, Tish
... View more