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
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;
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
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;
Thanks Ballardw.
Regards,
Sid
A data step with explicit output would be a good idea.
Thanks LinusH.
I didn't get your point over here.
Regards,
Sid
You might also want to look at A Better Way To Flip (Transpose) a SAS Dataset
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.