BookmarkSubscribeRSS Feed
Siddhartha
Calcite | Level 5

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

7 REPLIES 7
ballardw
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

Thanks Ballardw.

Regards,

Sid

LinusH
Tourmaline | Level 20

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

Data never sleeps
Siddhartha
Calcite | Level 5

Thanks LinusH.

I didn't get your point over here.

Regards,

Sid

PaigeMiller
Diamond | Level 26

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

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1321 views
  • 0 likes
  • 4 in conversation