proc transpose collapse multiple rows into single row

Q1983 — Sun, 18 Apr 2021 02:05:59 GMT

data have;

length catgy $25

month_ $5

tot_cnt 8

Past_Due 8;

input catgy -- Past_Due;

datalines;

MyCatgy JAN20 10 20

MyCatgy JAN20 22 46

MyCatgy FEB20 30 17

MyCatgy FEB20 60 32

MyCatgy MAR20 40 44

MyCatgy APR20 60 44

MyCatgy APR20 40 22

;

run;

data have2;

set have;

if Past_Due < 30 then current=1;

if Past_Due >= 30 and Past_Due <=40 then Past_Due1=1;

if Past_Due > 40 and Past_Due <= 50 then Past_Due2=1;

run;

proc sql;

create table have3 as

select catgy,month_,sum(tot_cnt) as total,sum(current) as curr,sum(Past_Due1) as Past1,

sum(Past_Due2) as Past2,

sum(Past_Due1)/sum(tot_cnt) as PastDue1Pct,

sum(Past_Due2)/sum(tot_cnt) as PastDue2Pct

from have2

group by catgy,month_

;quit;

proc transpose data = have3 out=want1 let;

id month_;

var total PastDue1Pct PastDue2Pct

;by catgy;

run;

Output

catgy	_NAME_	APR20	FEB20	JAN20	MAR20
MyCatgy	total	100	90	32	40
MyCatgy	PastDue1Pct		0.011111
MyCatgy	PastDue2Pct	0.01		0.03125	0.025


Desired Output - collapse row and move data up a row
catgy	_NAME_	APR20	FEB20	JAN20	MAR20
MyCatgy	total	100	90	32	40
MyCatgy	PastPCT	0.01	0.011111	0.03125	0.025

Is there a way to collapse the last row and move the data up and rename PastDue1_Pct into another name (ie PastDuePct) I need the data for both rows however I need everything to show on the same row. In addition if I have more than two rows I would need everything to show on that second row just bellow total. Is this possible??

Re: proc transpose collapse multiple rows into single row

Patrick — Sun, 18 Apr 2021 02:30:25 GMT

Given that your past_due columns are mutually exclusive (=only one of them can be populated at a time) I believe it would be much easier if you create only a single column with a code in it. Having a code in a single column should make anything downstream much easier for you.

/* option 1 */
data have2;
  set have;
  if Past_Due < 30 then 
    past_due_status=0;
  else
  if Past_Due >= 30 and Past_Due <=40 then
    past_due_status=1;
  else
  if Past_Due > 40 and Past_Due <= 50 then
    past_due_status=2;
run;

/* option 2 */
proc format;
  invalue past_due_status
    low -< 30 = 0
    30 -< 40  = 1
    40 -< 50  = 2
    50 - high = .
    ;
run;

data have2_x;
  set have;
  past_due_status=input(Past_Due,past_due_status.);
run;

topic proc transpose collapse multiple rows into single row in SAS Programming

proc transpose collapse multiple rows into single row

Re: proc transpose collapse multiple rows into single row