column to row transpose

[Siddhartha]

Hi,

I had five columns named as cust_id, cust_tin, cus_tin_suf, guar_tin, guar_tin_surf and the data is like this.

1,11,11111,22,22222

2,11,11111,22,22222

3,11,11111,22,22222.

Need to concatenate (cust-tin and cus_tin_surf) or (guar_tin and guar_tin with G as suffix) as tin.

I need output as follows

cust_id, tin

1, 11-11111

1G, 22-22222

2, 11-11111

2G, 22-22222

3, 11-11111

3G, 22-22222  

Can anyone advice me on this.

Regards,

Sid

[ballardw]

assuming that cust_id is large enough to accept a suffix. May have to specify a length before the SET statement if not.

Also, length of TIN might default to some longer than you want so might want to specify a length there as well.

data want (keep=cust_id tin);

     set have;

     tin = catx('-',cus_tin,cus_tin_suf); output;

     cust_id = cats(cust_id,'G');

     tin = catx('-', guar_tin, guar_tin_surf); output;     /* is the variable guar_tin_surf or guar_tin_suf? I used the one in your variable list*/

run;

[Siddhartha]

Thanks Ballardw.

But I am getting the error as follows:

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

      26:16  

NOTE: Invalid numeric data, '1G' , at line 26 column 16.

cust_id=. cust_tin=11 cust_tin_suf=11111 guar_tin=22 guar_tin_suf=22222 tin=22-22222 _ERROR_=1 _N_=1

NOTE: Invalid numeric data, '2G' , at line 26 column 16.

cust_id=. cust_tin=11 cust_tin_suf=11111 guar_tin=22 guar_tin_suf=22222 tin=22-22222 _ERROR_=1 _N_=2

NOTE: Invalid numeric data, '3G' , at line 26 column 16.

cust_id=. cust_tin=11 cust_tin_suf=11111 guar_tin=22 guar_tin_suf=22222 tin=22-22222 _ERROR_=1 _N_=3

Thanks & Regards,

Sid

[ballardw]

You didn't say which data type any of your variables were. Since cust_id is numeric (person preference: If I'm not going to do arithmetic with it then its character) you need to do some finagling.

data want (keep=new_cust_id tin); /* rename the new_cust_id if that's an issue*/

     length new_cust_id $ 10; /* or at least one character longer than cust_id appears*/

     set have ;

     new_cust_id = cats(cust_id);

     tin = catx('-',cus_tin,cus_tin_suf); output;

     New_cust_id = cats(cust_id,'G');

     tin = catx('-', guar_tin, guar_tin_surf); output;   

run;

[Siddhartha]

Thanks Ballardw.

Regards,

Sid

[LinusH]

A data step with explicit output would be a good idea.

[Siddhartha]

Thanks LinusH.

I didn't get your point over here.

Regards,

Sid

[PaigeMiller]

You might also want to look at A Better Way To Flip (Transpose) a SAS Dataset