The SAS Output Delivery System and reporting techniques

proc transpose or tabulate, Ideas needed

Reply
Frequent Contributor
Posts: 131

proc transpose or tabulate, Ideas needed

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

Super User
Super User
Posts: 7,392

Re: proc transpose or tabulate, Ideas needed

 

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.

Ask a Question
Discussion stats
  • 1 reply
  • 324 views
  • 0 likes
  • 2 in conversation