topic Re: Proc Transpose in SAS Programming

Proc Transpose

Eugenio211 — Mon, 31 Oct 2022 13:26:28 GMT

hello, hope all is well.

i just want to ask assistance with regards to proc transpose.

here is the step I used:

proc transpose data = CM_Summary1 out = CM_Summary2;
by location company rd dm district_name city Province;
var Loan_Count CashinVault over_short_amount;
id Month_Yr;
run;

data CM_Summary2; set CM_Summary2 (Rename=(_name_ = Type));
run;

below is the data I have and wanted to transform it, appreciate your help. Thanks a lot.

Have:

LOCATION	Type	Jun-22	Nov-21	Oct-21	Sep-22	Aug-22	Mar-22	Dec-21	May-22	Apr-22	Feb-22	Sep-21	Jul-22
1501	LOAN_COUNT	540	420	387	576	514	424	416	526	469	376	421	486
1501	CashinVault	80987.05	79897.57333	78148.96129	69626.38667	79937.85806	83572.34839	83061.85484	78898.60323	78319.87667	73662.84643	91947.10333	73506.62581
1501	OVER_SHORT_AMOUNT	209.27	365.36	116.8	230.52	95.54	328.84	2275.59	264.1	146.7	174.39	1296.13	268.14
1502	LOAN_COUNT	256	176	170	241	205	265	171	253	247	208	177	234
1502	CashinVault	55509.99333	71213.78333	67770.05484	57088.51667	55726.88387	54119.62333	80841.17097	56297.73226	66751.63	66131.54828	67713.91	45780.47419
1502	OVER_SHORT_AMOUNT	146.95	63.11	26.32	558.98	310.63	928.73	35.99	63.83	56.01	18.05	50.53	376.47
1503	LOAN_COUNT	70	67	71	93	70	75	77	56	70	52	74	68
1503	CashinVault	29612.56667	31737.79333	29720.32903	21833.52333	22838.31613	24837.18387	25869.07419	25504.86452	29162.34333	28571.59655	29001.40333	28043.39677
1503	OVER_SHORT_AMOUNT	1.78	661.3	1.23	3.4	11.15	3.9	1.89	0.3	1.18	0.77	0.7	17.28
1504	LOAN_COUNT	461	379	366	508	447	381	398	458	399	341	379	453
1504	CashinVault	68533.31667	59568.8	77498.27742	81046.77333	61071.40968	75150.73226	74853.99677	67773.72258	74162.11	82151.65357	69630.92667	78613.23548
1504	OVER_SHORT_AMOUNT	449.85	65.12	247.63	35	40.78	31.9	196.82	34	10.18	9.1	298.62	134.83

Want result:

LOCATION	Jun-22			Nov-21			Oct-21			Sep-22
	LOAN_COUNT	CashinVault	OVER_SHORT_AMOUNT	LOAN_COUNT	CashinVault	OVER_SHORT_AMOUNT	LOAN_COUNT	CashinVault	OVER_SHORT_AMOUNT	LOAN_COUNT	CashinVault	OVER_SHORT_AMOUNT
1501	540	80987.05	209.27	420	79897.57333	365.36	387	78148.96129	116.8	576	69626.38667	230.52
1502	256	55509.9933	146.95	176	71213.78333	63.11	170	67770.05484	26.32	241	57088.51667	558.98
1503	70	29612.5667	1.78	67	31737.79333	661.3	71	29720.32903	1.23	93	21833.52333	3.4
1504	461	68533.3167	449.85	379	59568.8	65.12	366	77498.27742	247.63	508	81046.77333	35

Re: Proc Transpose

PaigeMiller — Mon, 31 Oct 2022 13:44:03 GMT

We can't work from data provided in screen captures or Excel. We have to work from SAS data sets. You can provide a portion of the SAS data set by creating working SAS data step code, which you can type in yourself, or use this macro.

Is the WANT output supposed to be a SAS data set, or is it supposed to be a report?

The whole idea of putting calendar information into a SAS variable name is almost always a poor choice, and the subsequent programming will be more difficult. Better to use a long data set where you have the month and year in a variable, rather than in the variable name.

Lastly, I'm sure you know this, but variable names in SAS cannot have dashes in them.

Re: Proc Transpose

Kurt_Bremser — Mon, 31 Oct 2022 14:38:19 GMT

Your wanted result looks very much like a report done with PROC REPORT. Please post a sample of your initial dataset as @PaigeMiller already requested.

Re: Proc Transpose

PaigeMiller — Mon, 31 Oct 2022 14:53:41 GMT

Yes good point, @Kurt_Bremser, the original poster seems to have decided that PROC TRANSPOSE is the solution, but in some situations there are better solutions, such as possibly in this case PROC REPORT. So @Eugenio211 don't do this ... say you want a certain output without restricting the solution to one specific PROC.

Re: Proc Transpose

Tom — Mon, 31 Oct 2022 15:01:38 GMT

Read the values into a normalized structure to begin with:

data have;
  infile cards dsd dlm='|' truncover;
  input LOCATION $ Type :$20. @;
  array month[12] _temporary_ ;
  if _n_=1 then do;
    do i=1 to 12;
      input month[i] :monyy. @;
    end;
    delete;
  end;
  do i=1 to 12 ;
    input amount @;
    date = month[i];
    output;
  end;
  format date yymm7.;
  keep location type date amount;
cards;
Location|Type|Jun-22|Nov-21|Oct-21|Sep-22|Aug-22|Mar-22|Dec-21|May-22|Apr-22|Feb-22|Sep-21|Jul-22
1501|LOAN_COUNT|540|420|387|576|514|424|416|526|469|376|421|486
1501|CashinVault|80987.05|79897.57333|78148.96129|69626.38667|79937.85806|83572.34839|83061.85484|78898.60323|78319.87667|73662.84643|91947.10333|73506.62581
1501|OVER_SHORT_AMOUNT|209.27|365.36|116.8|230.52|95.54|328.84|2275.59|264.1|146.7|174.39|1296.13|268.14
1502|LOAN_COUNT|256|176|170|241|205|265|171|253|247|208|177|234
1502|CashinVault|55509.99333|71213.78333|67770.05484|57088.51667|55726.88387|54119.62333|80841.17097|56297.73226|66751.63|66131.54828|67713.91|45780.47419
1502|OVER_SHORT_AMOUNT|146.95|63.11|26.32|558.98|310.63|928.73|35.99|63.83|56.01|18.05|50.53|376.47
1503|LOAN_COUNT|70|67|71|93|70|75|77|56|70|52|74|68
1503|CashinVault|29612.56667|31737.79333|29720.32903|21833.52333|22838.31613|24837.18387|25869.07419|25504.86452|29162.34333|28571.59655|29001.40333|28043.39677
1503|OVER_SHORT_AMOUNT|1.78|661.3|1.23|3.4|11.15|3.9|1.89|0.3|1.18|0.77|0.7|17.28
1504|LOAN_COUNT|461|379|366|508|447|381|398|458|399|341|379|453
1504|CashinVault|68533.31667|59568.8|77498.27742|81046.77333|61071.40968|75150.73226|74853.99677|67773.72258|74162.11|82151.65357|69630.92667|78613.23548
1504|OVER_SHORT_AMOUNT|449.85|65.12|247.63|35|40.78|31.9|196.82|34|10.18|9.1|298.62|134.83
;

Then it is simple to produce that REPORT.

proc report data=have;
  column location amount,date,type ;
  define location / group;
  define date / across order=internal ' ';
  define type / across ' ';
  define amount / sum ' ';
run;

Re: Proc Transpose

ballardw — Mon, 31 Oct 2022 16:25:44 GMT

I really hate looking a "date" values that sort incorrectly. I expect a value in a column to the right to be a later date, or possibly sequentially decreasing if the purpose is to show most recent first. This makes me cringe:

Have:

LOCATION	Type	Jun-22	Nov-21	Oct-21	Sep-22	Aug-22	Mar-22	Dec-21	May-22	Apr-22	Feb-22	Sep-21	Jul-22
1501	LOAN_COUNT	540	420	387	576	514	424	416	526	469	376	421	486