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;
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
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 ® total;
run;
Haikuo
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.