BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anilgvdbm
Quartz | Level 8

Dear All,

I have a question regarding concatenating the household id and member id for my own research data.

that data looks like this

Household_IDMember_ID
IAP13B02915
IAP13B0291209
IAP13B02934ca
IAP13B029310
IAP13B0293201
IAP13B0293204
IAP13B02932
IAP13B02939
IAP13B02948

if i will merge both i need to add 2 zeroes  in front of 5 in 1st observation and 2nd observation i dont want to add please suggest me which function i have to use????

thanks

Anil

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input Household_ID : $20.     Member_ID $;
cards;
IAP13B0291     5
IAP13B0291     209
IAP13B0293     4ca
IAP13B0293     10
IAP13B0293     201
IAP13B0293     204
IAP13B0293     2
IAP13B0293     9
IAP13B0294     8
;
run;
data want;
 set have;
 length new $ 3;
 new=Member_ID;
 new=translate(right(new),'0',' ');
run;

Xia Keshan

View solution in original post

5 REPLIES 5
stat_sas
Ammonite | Level 13

If I understood this correctly then this can provide the desired output.

data want;

set have;

if _n_<3 then new_id=cats(Household_ID,'00',Member_ID);

else new_id=cats(Household_ID,Member_ID);

run;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

It looks to me as if at least one record has only two characters that "00" cat to would not solve (10).  I would suggest a padding algorithm, principally as the id is alpha-numeric (if it was numeric you could use z. format).

data have;
length household_id member_id $200;
Household_ID="IAP13B0291";  Member_ID="5"; output;
Household_ID="IAP13B0291";  Member_ID="209"; output;
Household_ID="IAP13B0293";  Member_ID="4ca"; output;
run;

data want;
  set have;
  if length(strip(member_id))=3 then household_id=cats(household_id,member_id);
  else household_id=cats(household_id,repeat('0',2-length(strip(member_id))),strip(member_id));
run;

Ksharp
Super User
data have;
input Household_ID : $20.     Member_ID $;
cards;
IAP13B0291     5
IAP13B0291     209
IAP13B0293     4ca
IAP13B0293     10
IAP13B0293     201
IAP13B0293     204
IAP13B0293     2
IAP13B0293     9
IAP13B0294     8
;
run;
data want;
 set have;
 length new $ 3;
 new=Member_ID;
 new=translate(right(new),'0',' ');
run;

Xia Keshan

anilgvdbm
Quartz | Level 8

Hi Xia Keshan,

It is correct method thank for the help...

thank you all for your suggestions..

Regards,

Anil

Reeza
Super User

Please mark as answered Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1211 views
  • 1 like
  • 5 in conversation