proc transpse

[gnt1986]

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;

[Jagadishkatam]

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

[Haikuo]

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