Proc Transpose help

[Eugenio211]

Hi,

I have the data below and I want to transpose this using 'PAST_DUE' as column.  Can someone please help? thanks a lot!

Location	PAST_DUE	PORTFOLIO_AMT
1001	1-30 Days	917743.04
1002	121-150 Days	2064.4
1003	151-180 Days	862.5
1004	181+ Days	10851.06
1005	31-60 Days	437245.32
1006	61-90 Days	310566.7
1007	91-120 Days	14462.44
1008	Aged Gross Charge Off	300924.87
1009	Current	21937142.14
1010	Gross Charge Off	604973.24
1011	Interest Income	1254249.72
1012	Net Charge Off	418241.37
1013	Non-Aged Gross Charge Off	101701.38
1014	1-30 Days	673340.37
1015	121-150 Days	11531.73
1016	151-180 Days	11231.39
1017	181+ Days	34851.08
1018	31-60 Days	301053.51
1019	61-90 Days	238534.36
1020	91-120 Days	32746.51
1021	Aged Gross Charge Off	195522.19
1022	Current	16515694.87
1023	Gross Charge Off	497445.05
1024	Interest Income	976155.1
1025	Net Charge Off	314109.44
1026	Non-Aged Gross Charge Off	98835.61
1027	1-30 Days	810896.25
1028	121-150 Days	32545.81
1029	151-180 Days	17769.4
1030	181+ Days	18107.64
1031	31-60 Days	415263.76
1032	61-90 Days	314093.92
1033	91-120 Days	54125.37
1034	Aged Gross Charge Off	259291.43
1035	Current	19451736.13
1036	Gross Charge Off	506609.35
1037	Interest Income	1056621.56
1038	Net Charge Off	374537.79
1039	Non-Aged Gross Charge Off	97618.86

Want:

Location	1-30 Days	121-150 Days	151-180 Days	181+ Days	31-60 Days	61-90 Days	91-120 Days	Aged Gross Charge Off	Current	Gross Charge Off	Interest Income	Net Charge Off	Non-Aged Gross Charge Off
1001	917743	2064.4	862.5	10851.06	437245.3	310566.7	14462.44	300924.9	21937142	604973.2	1254250	418241.4	101701.4

[Reeza]

proc transpose data=have;
by location;
id past_due;
idlabel past_due;
var portfolio_amt;
run;

If you want to control the order of the variables you'll need to add another variable to control the order.

[ballardw]

Without actual data then something like

Proc sort data=yourdataset;
   by location past_due;
run;

Proc transpose data=yourdataset out=want (drop=_name_);
   by location;
   id label past_due;
   var portfolio_amt;
run;

This will create the variables with NAMES like Col1 but assign the Past_due value to the label of the variables.

You really do not want variables with those names.

OR perhaps you only want a report? Those wide data sets are harder to work with for many purposes:

proc report data=yourdataset;
   columns location past_due,portfolio_amt;
   define location / group;
   define past_due/ across;
   define portfolio_amt/ ""; 
run;

The " " in the define for portfolio_amt is to suppress the column heading that would appear under the past_due value.

You could do the same with the Past_due define as well.

[A_Kh]

Basically you could use Proc transpose with ID statement for PAST_DUE variable, to transform it into wide format.

However, once transposed data values in the PAST_DUE column get converted into Variable name in compliance with sas variable naming  conventions (starting either with letters or underscore, containing letter, underscore or numbers, and not longer than 32 character). This will transform spaces or special characters into underscore, truncate long strings if more than 32 characters. 

Besides, the output will not look like as you shown above as your location values are different. There will be one record per distinct location. 

eg. try this and see;

proc transpose data= have out=want;
by location;
var portfolio_amt;
id past_due;
run;

[Tom]

The requested output appears to have nothing to do with the sample input.  For example for location 1001 there is only one observation, so the output would have missing values for all of the other columns.

Please either fix the output to reflect the input, or share input that would produce that output.