CODEWITHSUNDEEP
sastranspose
LM12345
LM12345

Reputation: 11

Using Proc Transpose Wide to Long in SAS

I have data that looks like the table below and i would like to transpose it much like i would with a pivot table in excel, but instead in SAS. I've tried proc transpose but get "ERROR: The ID value "'**'n" occurs twice in the same BY group."

Have (call this Long):

Class Year Dev_Qtr Paid_Loss Inc_Loss
Segment A 2004 3 1000000 1500000
Segment A 2004 6 950547 1425820.5
Segment A 2004 9 971746 1457619
Segment A 2004 15 912356 1368534
Segment A 2004 18 26916 40374
Segment A 2004 21 235696 353544
Segment A 2004 27 641805 962707.5
Segment A 2004 30 653554 980331
Segment B 2004 3 1000000 1500000
Segment B 2004 6 634698 952047
Segment B 2004 9 956788 1435182
Segment B 2004 15 18532 27798
Segment B 2004 18 686824 1030236
Segment B 2004 21 289675 434512.5
Segment B 2004 27 462303 693454.5
Segment B 2004 30 591880 887820

Want (call this Wide):

Class Year Amount 3 6 9 12 15 18 21 24 27 30
Segment A 2004 USD_Paid_Loss 1000000 950547 971746 912356 26916 235696 641805 653554
Segment A 2004 USD_Inc_Loss 1500000 1425820.5 1457619 1368534 40374 353544 962707.5 980331
Segment B 2004 USD_Paid_Loss 1000000 634698 956788 18532 686824 289675 462303 591880
Segment B 2004 USD_Inc_Loss 1500000 952047 1435182 27798 1030236 434512.5 693454.5 887820

The code i tried running is

proc transpose data=Long out=Wide name=Amount;      
     id Dev_Qtr;        
     var Paid_Loss Inc_Loss;        
     by Class Year;     
run;

and the error is "ERROR: The ID value "'**'n" occurs twice in the same BY group."

Is it possible to create an output like i'm trying to achieve in SAS with proc transpose or is there some better way to do this? The example of data i'm showing has 2 classes across the same year for various quarters but the real data will extend across 10 years of history in this manner. Maybe this requires some looping?

Would appreciate any help! I'm by no means a SAS expert and this clearly seems outside my usual wheelhouse!

Upvotes: 1

Views: 312

Answers (2)

TCamara
TCamara

Reputation: 179

Hm. Maybe it's how you are reading in the data? I just gave this a try and it runs smoothly.

  1. Here is how I created the data.
data long_data;
input Class $9. Year dev_qtr paid_loss inc_loss;
cards;
Segment_A 2004 3 1000000 1500000 
Segment_A 2004 6 950547 1425820.5
Segment_A 2004 9 971746 1457619
Segment_A 2004 15 912356 1368534
Segment_A 2004 18 26916 40374
Segment_A 2004 21 235696 353544
Segment_A 2004 27 641805 962707.5
Segment_A 2004 30 653554 980331
Segment_B 2004 3 1000000 1500000
Segment_B 2004 6 634698 952047
Segment_B 2004 9 956788 1435182
Segment_B 2004 15 18532 27798
Segment_B 2004 18 686824 1030236
Segment_B 2004 21 289675 434512.5
Segment_B 2004 27 462303 693454.5
Segment_B 2004 30 591880 887820
;
run;
  1. Here is the proc transpose (unchanged).
proc transpose data=long_data out=Wide name=Amount;
id dev_Qtr; by class year; 
var paid_loss inc_loss;        
run;

The log is happy and the output data as expected.

Upvotes: 0

PBulls
PBulls

Reputation: 1711

Just to summarize the comment discussion:

  • For a long to wide transpose you can only have a single record for each combination of by and id variable values. This is what the error indicates.
  • id uses formatted values, so while the underlying numeric values were unique their formatted ones were not (due to a format of insufficient width, probably). PROC TRANSPOSE tries to create the same variable within a by group twice and errors out. The fix was in the format.

Upvotes: 1

Related Questions