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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.