topic Re: proc transpse in SAS Procedures

proc transpse

gnt1986 — Mon, 04 Nov 2013 01:50:40 GMT

I have a attached an excel spreadsheet as follows; ColumnA is a set of loans that need to be counted. Column B is the label to be shown vertically(rows). Column C is similar to Column B however it needs to be shown horizontally (column). I need to do a proc transpose or something similar to display the final dataset just like the pivot tab in the spreadsheet. Notice that some of the fields in the column B (row) have spaces. How can I account for this. Would I need to do a proc format? The name of the initial dataset is

data region;

set region2;

run;

Re: proc transpse

Jagadishkatam — Mon, 04 Nov 2013 02:22:47 GMT

data have;

input Loan RegionThisMth & $14. RegionLastMth & $14.;

cards;

1122 Southwest 1233 Southwest 1233

22344 Southwest 1234 Southwest 1234

234 Southwest 1235 Southwest 1235

456 Northwest Eastern

1355 Eastern Northern 234

2356 Northern 234 Eastern

456 Western Western

156 Southwest 1233 Southwest 1233

2241 Southwest 1234 Southwest 1234

1544 Southwest 1235 Southwest 1235

361 Northwest Northwest

1253 Eastern Eastern

216 Northern 234 Northern 234

31 Western Western

;

run;

ods output CrossTabFreqs=CrossTabFreqs(keep=Frequency RegionThisMth RegionLastMth );

proc freq data=have;

table RegionThisMth*RegionLastMth;

run;

data want;

length RegionLastMth RegionThisMth $ 20;

set CrossTabFreqs;

if RegionThisMth='' then do;

RegionThisMth='Grand Total';

order=1;

end;

else do;

RegionThisMth=RegionThisMth;

order=0;

end;

if RegionLastMth='' then RegionLastMth='Grand Total';

run;

proc sort data=want;

by order RegionThisMth;

run;

proc transpose data=want out=trans(drop=order _name_ _label_);

by order RegionThisMth;

id RegionLastMth;

var Frequency;

run;

Thanks,

Jagadish

Re: proc transpse

Haikuo — Tue, 05 Nov 2013 05:36:32 GMT

An alternative:

data have;

input Loan RegionThisMth & $14. RegionLastMth & $14.;

cards;

1122 Southwest 1233 Southwest 1233

22344 Southwest 1234 Southwest 1234

234 Southwest 1235 Southwest 1235

456 Northwest Eastern

1355 Eastern Northern 234

2356 Northern 234 Eastern

456 Western Western

156 Southwest 1233 Southwest 1233

2241 Southwest 1234 Southwest 1234

1544 Southwest 1235 Southwest 1235

361 Northwest Northwest

1253 Eastern Eastern

216 Northern 234 Northern 234

31 Western Western

;

run;

proc sql noprint;

select distinct quote(compress(RegionThisMth)) into :hreg separated by ', ' from have;

select distinct quote(compress(RegionThisMth)) into :areg separated by ' ' from have;

select distinct compress(RegionThisMth) into :reg separated by ' ' from have;

select count(distinct compress(RegionThisMth)) into :ct trimmed from have;

quit;

data want;

if _n_=1 then do;

declare hash h(ordered:'y');

h.definekey('RegionThisMth');

h.definedata('RegionThisMth', &hreg, 'total');

h.definedone();

declare hiter hi('h');

end;

set have end=last;

array t1 ®

array t2(7) $20 _temporary_ (&areg);

array t3(7);

RegionLastMth=compress(RegionLastMth);

index=whichc(RegionLastMth, of t2(*));

put RegionThisMth= index= RegionLastMth=;

rc=h.find();

t1(index)+1; put t1(index)=;

total=sum(of t1(*));

rc=h.replace();

call missing (of _all_);

if last then do;

do rc=hi.first() by 0 while (rc=0);

do i=1 to dim(t3);

t3(i)+t1(i);

end;

output;

rc=hi.next();

end;

do i=1 to dim(t3);

t1(i)=t3(i);

end;

RegionThisMth='Grand Total';

Total=sum(of t1(*));

output;

stop;

end;

keep RegionThisMth &reg total;

run;

Haikuo