topic Re: column to row transpose in SAS Programming

column to row transpose

Siddhartha — Tue, 30 Sep 2014 18:29:33 GMT

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

Re: column to row transpose

ballardw — Tue, 30 Sep 2014 18:52:13 GMT

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;

Re: column to row transpose

LinusH — Tue, 30 Sep 2014 18:54:02 GMT

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

Re: column to row transpose

Siddhartha — Tue, 30 Sep 2014 19:01:14 GMT

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

Re: column to row transpose

Siddhartha — Tue, 30 Sep 2014 19:19:37 GMT

Thanks LinusH.

I didn't get your point over here.

Regards,

Sid

Re: column to row transpose

ballardw — Tue, 30 Sep 2014 19:23:26 GMT

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;

Re: column to row transpose

Siddhartha — Tue, 30 Sep 2014 19:28:37 GMT

Thanks Ballardw.

Regards,

Sid

Re: column to row transpose

PaigeMiller — Tue, 30 Sep 2014 20:04:29 GMT

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