Channel1 | report_dt_p1 | Metric | count | gross_amt | gross_amt_avg | fico | high_risk | fico_lt660 | ltv_gt90 |
Emerging Banker | Mar2015 | STM | 268 | 99.39263 | 370.868 | 773.0039 | High Risk Categories | 0 | 0.01701761 |
Emerging Banker | Apr2015 | STM | 206 | 74.67056 | 362.4784 | 770.9701 | High Risk Categories | 0.00691839 | 0.037584412 |
Emerging Banker | May2015 | STM | 204 | 70.71112 | 346.6232 | 768.778 | High Risk Categories | 0.007555388 | 0.058201864 |
Emerging Banker | Jun2015 | STM | 220 | 72.48243 | 329.4656 | 770.7628 | High Risk Categories | 0.002306766 | 0.030465647 |
Emerging Banker | Jul2015 | STM | 261 | 85.40261 | 327.2131 | 764.0758 | High Risk Categories | 0.010602721 | 0.068190841 |
Emerging Banker | Aug2015 | STM | 231 | 65.51545 | 283.6167 | 764.4192 | High Risk Categories | 0.008735344 | 0.094368355 |
Emerging Banker | Sep2015 | STM | 328 | 98.53859 | 300.4225 | 766.0572 | High Risk Categories | 0.00046214 | 0.06103769 |
Emerging Banker | Oct2015 | STM | 307 | 90.8371 | 295.8863 | 763.8919 | High Risk Categories | 0.007851968 | 0.07552668 |
Emerging Banker | |||||||||
Mar-15 | Apr-15 | May-15 | Jun-15 | Jul-15 | Aug-15 | ||||
Metric | STM | STM | STM | STM | STM | STM | |||
Count | 268 | 206 | 204 | 220 | 261 | 231 | |||
Loan Amt ($M) | 99M | 75M | 71M | 72M | 85M | 66M | |||
Avg Loan Amt ($K) | 371K | 362K | 347K | 329K | 327K | 284K | |||
FICO | 773 | 771 | 769 | 771 | 764 | 764 | |||
High Risk Categories | |||||||||
% FICO <660 | 0.0% | 0.7% | 0.8% | 0.2% | 1.1% | 0.9% | |||
% LTV >90 | 1.7% | 3.8% | 5.8% | 3.0% | 6.8% | 9.4% |
I am attempting to convert the above data into a transposed or tabulated summary. I am trying to determine the best way to handle this
Hi
In future, can you post test data as a datastep. You can use a simple tranpose on the data, and then process that into what you want:
data have; input Channel1 $ report_dt_p1 $ Metric $ count gross_amt gross_amt_avg fico high_risk $ fico_lt660 ltv_gt90; datalines; Emerging_Banker Mar2015 STM 268 99.39263 370.868 773.0039 High_Risk_Categories 0 0.01701761 Emerging_Banker Apr2015 STM 206 74.67056 362.4784 770.9701 High_Risk_Categories 0.00691839 0.037584412 ; run; proc transpose data=have out=want; id report_dt_p1; run; data want; set want; length test $50; array month{2} $50.; select(_name_); when('count') do; test="Count"; month{1}=strip(put(mar2015,best.)); month{2}=strip(put(apr2015,best.)); end; when('gross_amt') do; test="Gross Amt"; month{1}=cats(put(ceil(mar2015),best.),"M"); month{2}=cats(put(ceil(apr2015),best.),"M"); end; otherwise; end;
I have only done a few of the conversions for you to give you an idea on how to go. Obviously you can shrink the code a bit by arraying the months for example.
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.