Hello,
I currently have a dataset that looks like the dataset below - for each account, the perf_month represents a beginning observation month, and the rate_month1,2...n columns contain rates over the subsequent months. The actual subsequent months are in the columns perf_month1,2...n. My example only shows 2 months, in reality the number of months is around 150.
Input data
DATA test;
INPUT Acctnum perf_month rate_month1 rate_month2 perf_month1 perf_month2;
CARDS;
123 31-Oct-17 0.21 0.00 30-Nov-17 31-Dec-17
456 31-Oct-17 0.94 2.35 30-Nov-17 31-Dec-17
789 31-Oct-17 0.53 0.44 30-Nov-17 31-Dec-17
234 31-Oct-17 0.00 0.00 30-Nov-17 31-Dec-17
123 30-Nov-17 0.00 . 31-Dec-17 .
456 30-Nov-17 0.65 . 31-Dec-17 .
789 30-Nov-17 0.45 . 31-Dec-17 .
234 30-Nov-17 0.00 . 31-Dec-17 .
;
RUN ;
I need to create averages of the rates, and display the frequencies by month. I need to create 2 outputs that look like the tables below. Any tips on how to do this would be greatly appreciated, thanks!
Output 1
perf_month Month1 Month2
Oct-17 0.56 0 .93
Nov-17 0.28 0.00
Output 2
perf_month Nov-17 Dec-17
Oct-17 0.56 0.93
Nov-17 0.28 0.00
You would be wise to create a long narrow dataset rather than a very wide dataset for this data.
The variables would be
Acctnum perf_month rate rate_month_number
This way you can run a simple PROC SUMMARY and get the results you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.