CODEWITHSUNDEEP
sastranspose
karl
karl

Reputation: 19

Rearranging data in SAS

I'm pretty new in SAS, so I'm struggling to find out how to rearrange my data. My data set looks like this:


CPT    DATE         A        B           C        D       etc.
1      date1        20.000   5.000       0        0
1      date2        0        0           0        30.000
1      date3        0        10.000      10.000   0
2      date1        3.000    3.000       0        0
2      date2        0        0           5.000    3.000 
etc.

where cpt(i) represents each counterparty, date(i) represents the date of my cash flows and A,B,C,D are the different types of cash flows. Since this dataset has lots of columns, I'd like to rearrange the data by increasing the number of rows when there is more than one cash flow in date(i). So the output is supposed to be this one:

CPT          DATE         Cash Flow     Type
1            date1        20.000          A
1            date1         5.000          B
1            date2        30.000          D
1            date3        10.000          B
1            date3        10.000          C  
2            date1         3.000          A
2            date2         3.000          B
2            date3         5.000          C
2            date4         3.000          D
etc.

Any tips on how to get what I want? Cheers

Datalines format of data is below.

data have;
    input CPT DATE$ A B C D;
    format a b c d 8.3;
    datalines;
1 date1 20.000 5.000 0 0
1 date2 0 0 0 30.000
1 date3 0 10.000 10.000 0
2 date1 3.000 3.000 0 0
2 date2 0 0 5.000 3.000 
;
run;

Upvotes: 1

Views: 63

Answers (3)

Joe
Joe

Reputation: 63424

This is a 'wide to long' transpose. It's really easy!

data have;
input CPT    DATE   $ A        B           C        D   ;
datalines;
1      date1        20.000   5.000       0        0
1      date2        0        0           0        30.000
1      date3        0        10.000      10.000   0
2      date1        3.000    3.000       0        0
2      date2        0        0           5.000    3.000 
;;;;
run;

proc transpose data=have out=want;
  by cpt date;
  var a b c d;
run;

If there are more complexities than this, you can also do this in the data step.

Upvotes: 3

Richard
Richard

Reputation: 27498

A more tricked out TRANSPOSE can set the pivot column label and restrict the output to non-zero cashflow.

proc transpose data=have 
  out=want(
    rename=(_name_=Type col1=cashflow)
    where=(cashflow ne 0) 
  )
;
  by cpt date;
  var a b c d;
  label cashflow='Cash Flow';
run;

You will have to endure a log message

WARNING: Variable CASHFLOW not found in data set WORK.HAVE.

Upvotes: 1

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

Use proc transpose. It's the easiest way to transpose any data in SAS. It'll automatically rename variable column names to COL1, COL2, etc. Use the rename= output dataset option to rename your variable to cash_flow.

proc transpose data = have
               out  = want(rename=(COL1 = cash_flow) )
               name = type
               ;
    by cpt date;
run;

Upvotes: 2

Related Questions