proc transpose or tabulate, Ideas needed

Q1983 — Fri, 27 Nov 2015 03:28:31 GMT

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

Re: proc transpose or tabulate, Ideas needed

RW9 — Fri, 27 Nov 2015 10:08:56 GMT

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.

topic Re: proc transpose or tabulate, Ideas needed in ODS and Base Reporting

proc transpose or tabulate, Ideas needed

Re: proc transpose or tabulate, Ideas needed