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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.