cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
mona4u
Lapis Lazuli | Level 10 mona4u
Lapis Lazuli | Level 10

complicated conversion

Posted 08-20-2018 03:14 PM (3425 views)

I have this issue with the data set  I'm basically want to get this result from the data set I posted below. 

the way the variable converted came within subjid  R101=TL01  and R102=TL02  .....ETC. 

But the confusing part is R20X take the next number available after R10X conversion for example if TL02 already reserved the R201 will convert to TL03, R202 converts to TL04. 

R301 convert to NT01

R302 converts to NTO2

.. ETC.

R401 converts to NL01
and
R402 converts to NL02.

 

here is the example 

 

SUBJIDLESNIDConverted
C12-123R101TL01
C12-123R102TL02
C12-123R201TL03
C12-123R301NT01
C12-123R401NL01
C12-123R101TL01
C12-123R102TL02
C12-123R201TL03
C12-123R301NT01
C12-123R401NL01
C14-062R101TL01
C14-062R201TL02
C14-062R301NT01
C14-062R401NL01
C14-062R402NL02
C14-062R101TL01
C14-062R201TL02
C14-062R301NT01
C14-062R401NL01
C14-062R402NL02
C14-062R101TL01
C14-062R201TL02
C14-062R301NT01
C14-062R401NL01
C14-062R402NL02
C14-062R101TL01
C14-062R201TL02
C14-062R301NT01

 

this is a sample of the data I have 

data have;

length SUbJID $7 LESNID $5;

input SUbJID $ LESNID $;

datalines;

C12-123         R101

C12-123         R102

C12-123         R201

C12-123         R301

C12-123         R401

C12-123         R101

C12-123         R102

C12-123         R201

C12-123         R301

C12-123         R401

C14-062         R101

C14-062         R201

C14-062         R301

C14-062         R401

C14-062         R402

C14-062         R101

C14-062         R201

C14-062         R301

C14-062         R401

C14-062         R402

C14-062         R101

C14-062         R201

C14-062         R301

C14-062         R401

C14-062         R402

C14-062         R101

C14-062         R201

C14-062         R301; 

run; 



0 Likes
Reply
11 REPLIES 11
art297
Opal | Level 21 art297
Opal | Level 21

Re: complicated conversion

Posted 08-20-2018 04:19 PM (3396 views)  |   In reply to mona4u

If your data are already in the order shown in your example then something like the following should work:

data want;
  set have;
  if substr(LESNID,1,2) in ('R1','R2') then prefix='TL';
  else if substr(LESNID,1,2) eq 'R3' then prefix='NT';
  else if substr(LESNID,1,2) eq 'R4' then prefix='NL';
run;

data want (drop=counter prefix);
  set want;
  by SUbJID prefix notsorted;
  if first.prefix then counter=1;
  else counter+1;
  converted=catt(prefix,put(counter,z2.));
run;

Art, CEO, AnalystFinder.com

 

0 Likes
Reply
mona4u
Lapis Lazuli | Level 10 mona4u
Lapis Lazuli | Level 10

Re: complicated conversion

Posted 08-28-2018 03:12 PM (3345 views)  |   In reply to art297
sorry for taking the time to get back to you today I tried to validate the program with a big data set and I found that there is issue with the conversion with some cases



SUbJID LESNID converted should be
C12-123 R101 TL01
C12-123 R102 TL02
C12-123 R201 TL03
C12-123 R301 NT01
C12-123 R401 NL01
C12-123 R101 TL01
C12-123 R102 TL02
C12-123 R201 TL03
C12-123 R301 NT01
C12-123 R401 NL01
C12-124 R201 TL01
C12-124 R401 NL01
C12-124 R201 TL01
C12-124 R401 NL01
C12-124 R201 TL01
C12-124 R401 NL01
C12-124 R201 TL01
C12-125 R101 TL01
C12-125 R101 TL02
C12-126 R201 TL01
C12-126 R301 NT01
C12-126 R201 TL01
C12-126 R301 NT01
C12-126 R201 TL01
C12-126 R301 NT01
C12-789 R301 NT01 NT01
C12-789 R301 NT02 NT01
C12-789 R301 NT03 NT01
C12-789 R301 NT04 NT01
C14-011 R101 TL01
C14-011 R201 TL02
C14-011 R301 NT01
C14-011 R101 TL01
C14-011 R201 TL02
C14-011 R301 NT01
C14-011 R101 TL01
C14-011 R201 TL02
C14-011 R301 NT01
C14-012 R101 TL01
C14-012 R201 TL02
C14-012 R301 NT01
C14-012 R101 TL01
C14-012 R201 TL02
C14-012 R301 NT01
C14-012 R101 TL01
C14-012 R201 TL02
C14-012 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-014 R101 TL01
C14-014 R201 TL02
C14-014 R301 NT01
C14-014 R101 TL01
C14-014 R201 TL02
C14-014 R301 NT01
C14-014 R101 TL01
C14-014 R201 TL02
C14-014 R301 NT01
C14-014 R101 TL01
C14-014 R201 TL02
C14-014 R301 NT01
C14-015 R101 TL01
C14-015 R201 TL02
C14-015 R301 NT01
C14-015 R101 TL01
C14-015 R201 TL02
C14-015 R301 NT01
C14-015 R101 TL01
C14-015 R201 TL02
C14-015 R301 NT01
C14-016 R101 TL01
C14-016 R201 TL02
C14-016 R301 NT01
C14-016 R101 TL01
C14-016 R201 TL02
C14-016 R301 NT01
C14-016 R101 TL01
C14-016 R201 TL02
C14-016 R301 NT01
C14-016 R101 TL01
C14-016 R201 TL02
C14-016 R301 NT01
C14-017 R101 TL01 TL01
C14-017 R102 TL02 TL02
C14-017 R201 TL03 TL03
C14-017 R101 TL04 TL01
C14-017 R102 TL05 TL02
C14-017 R201 TL06 TL03
C14-017 R101 TL07 TL01
C14-017 R102 TL08 TL02
C14-017 R201 TL09 TL03
C14-017 R101 TL10 TL01
C14-017 R102 TL11 TL02
C14-017 R201 TL12 TL03


Can you take a look and fix the issue for me ?
0 Likes
Reply
art297
Opal | Level 21 art297
Opal | Level 21

Re: complicated conversion

Posted 08-28-2018 04:17 PM (3342 views)  |   In reply to mona4u

I think some of your desired assignments are wrong (i.e., they don't follow your rules).

 

Regardless, this is the first time you mentioned that you have duplicates that should be assigned previous codes.

 

Try the following:

data want;
  set have;
  if substr(LESNID,1,2) in ('R1','R2') then prefix='TL';
  else if substr(LESNID,1,2) eq 'R3' then prefix='NT';
  else if substr(LESNID,1,2) eq 'R4' then prefix='NL';
run;

data want (drop=counter prefix);
  set want;
  array assigns(999) _temporary_;
  by SUbJID prefix notsorted;
  if first.prefix then do;
    counter=1;
    call missing(of assigns(*));
    assigns(input(substr(LESNID,2),8.))=counter;
  end;
  else if missing(assigns(input(substr(LESNID,2),8.))) then do;
    counter+1;
    assigns(input(substr(LESNID,2),8.))=counter;
  end;
  converted=catt(prefix,put(assigns(input(substr(LESNID,2),8.)),z2.));
run;

Art, CEO, AnalystFinder.com

 

0 Likes
Reply
mona4u
Lapis Lazuli | Level 10 mona4u
Lapis Lazuli | Level 10

Re: complicated conversion

Posted 08-28-2018 05:39 PM (3336 views)  |   In reply to art297
the last update was totally correct but I'm so sorry for the last minute request. My manager just told me that I need also to map R3010 to NT10
R3011 to NT11
R3012 to NT12
.......
R3020 to NT20

...
etc.
Can you help me with this as well?
this is a mock-up data

data have;
length SUbJID $7 LESNID $5;
input SUbJID $ LESNID $;
datalines;
C12-123 R101
C12-123 R102
C12-123 R201
C12-123 R301
C12-123 R401
C12-123 R101
C12-123 R102
C12-123 R201
C12-123 R301
C12-123 R401
C12-124 R201
C12-124 R401
C12-124 R201
C12-124 R401
C12-124 R201
C12-124 R401
C12-124 R201
C12-125 R101
C12-125 R101
C12-126 R201
C12-126 R301
C12-126 R201
C12-126 R301
C12-126 R201
C12-126 R301
C12-789 R301
C12-789 R301
C12-789 R301
C12-789 R301
C14-011 R101
C14-011 R201
C14-011 R301
C14-011 R101
C14-011 R201
C14-011 R301
C14-011 R101
C14-011 R201
C14-011 R301
C14-012 R101
C14-012 R201
C14-012 R301
C14-012 R101
C14-012 R201
C14-012 R301
C14-012 R101
C14-012 R201
C14-012 R301
C14-013 R101
C14-013 R201
C14-013 R301
C14-013 R101
C14-013 R201
C14-013 R301
C14-013 R101
C14-013 R201
C14-013 R301
C14-013 R101
C14-013 R201
C14-013 R301
C14-014 R101
C14-014 R201
C14-014 R301
C14-014 R101
C14-014 R201
C14-014 R301
C14-014 R101
C14-014 R201
C14-014 R301
C14-014 R101
C14-014 R201
C14-014 R301
C14-015 R101
C14-015 R201
C14-015 R301
C14-015 R101
C14-015 R201
C14-015 R301
C14-015 R101
C14-015 R201
C14-015 R301
C14-016 R101
C14-016 R201
C14-016 R301
C14-016 R101
C14-016 R201
C14-016 R301
C14-016 R101
C14-016 R201
C14-016 R301
C14-016 R101
C14-016 R201
C14-016 R301
C14-017 R101
C14-017 R102
C14-017 R201
C14-017 R101
C14-017 R102
C14-017 R201
C14-017 R101
C14-017 R102
C14-017 R201
C14-017 R101
C14-017 R102
C14-017 R201
C14-018 R101
C14-018 R201
C14-018 R101
C14-018 R201
C14-018 R101
C14-018 R201
C14-018 R101
C14-018 R201
C14-022 R101
C14-022 R201
C14-022 R301
C14-022 R101
C14-022 R201
C14-022 R301
C14-022 R101
C14-022 R201
C14-022 R301
C14-022 R101
C14-022 R201
C14-022 R301
C14-023 R101
C14-023 R201
C14-023 R301
C14-023 R101
C14-023 R201
C14-023 R301
C14-023 R101
C14-023 R201
C14-023 R301
C14-023 R101
C14-023 R201
C14-023 R301
C14-024 R101
C14-024 R201
C14-024 R301
C14-024 R101
C14-024 R201
C14-024 R301
C14-024 R101
C14-024 R201
C14-024 R301
C14-024 R101
C14-024 R201
C14-024 R301
C14-025 R101
C14-025 R201
C14-025 R301
C14-025 R101
C14-025 R201
C14-025 R301
C14-025 R101
C14-025 R201
C14-025 R301
C14-025 R101
C14-025 R201
C14-025 R301
C14-026 R101
C14-026 R201
C14-026 R301
C14-026 R101
C14-026 R201
C14-026 R301
C14-026 R101
C14-026 R201
C14-026 R301
C14-027 R101
C14-027 R201
C14-027 R301
C14-027 R101
C14-027 R201
C14-027 R301
C14-027 R101
C14-027 R201
C14-027 R301
C14-027 R101
C14-027 R201
C14-027 R301
C14-028 R101
C14-028 R102
C14-028 R201
C14-028 R301
C14-028 R101
C14-028 R102
C14-028 R201
C14-028 R301
C14-056 R101
C14-056 R201
C14-056 R101
C14-056 R201
C14-056 R401
C14-056 R101
C14-056 R201
C14-060 R101
C14-060 R201
C14-060 R202
C14-060 R301
C14-060 R303
C14-060 R304
C14-060 R305
C14-060 R307
C14-060 R3010
C14-060 R3011
C14-060 R3012
C14-060 R101
C14-060 R201
C14-060 R202
C14-060 R301
C14-060 R303
C14-060 R304
C14-060 R305
C14-060 R307
C14-060 R3010
C14-060 R3011
C14-060 R3012
C14-062 R101
C14-062 R201
C14-062 R301
C14-062 R401
C14-062 R402
C14-062 R101
C14-062 R201
C14-062 R301
C14-062 R401
C14-062 R402
C14-062 R101
C14-062 R201
C14-062 R301
C14-062 R401
C14-062 R402
C14-062 R101
C14-062 R201
C14-062 R301
C14-062 R101
C14-062 R201
C14-062 R301
C14-062 R401
C14-062 R402
C14-062 R101
C14-062 R201
C14-062 R301
C14-062 R401
C14-062 R402
C14-062 R101
C14-062 R201
C14-062 R301
C99-001 R101
C99-001 R102
C99-001 R201
C99-001 R202
C99-001 R301


;
run;


0 Likes
Reply
art297
Opal | Level 21 art297
Opal | Level 21

Re: complicated conversion

Posted 08-28-2018 05:51 PM (3335 views)  |   In reply to mona4u

Easy enough, but study the code so that you can make such changes/additions on your own:

data want;
  set have;
  if substr(LESNID,1,2) in ('R1','R2') then prefix='TL';
  else if substr(LESNID,1,2) eq 'R3' then prefix='NT';
  else if substr(LESNID,1,2) eq 'R4' then prefix='NL';
run;

data want (drop=counter prefix);
  set want;
  array assigns(999) _temporary_;
  by SUbJID prefix notsorted;
  if length(LESNID) gt 4 then do;
    converted=catt(prefix,substr(LESNID,4,2));
  end;
  else do;
    if first.prefix then do;
      counter=1;
      call missing(of assigns(*));
      assigns(input(substr(LESNID,2),8.))=counter;
    end;
    else if missing(assigns(input(substr(LESNID,2),8.))) then do;
      counter+1;
      assigns(input(substr(LESNID,2),8.))=counter;
    end;
    converted=catt(prefix,put(assigns(input(substr(LESNID,2),8.)),z2.));
  end;
run;

Art, CEO, AnalystFinder.com

 

0 Likes
Reply
Haikuo
Onyx | Level 15 Haikuo
Onyx | Level 15

Re: complicated conversion

Posted 08-20-2018 04:29 PM (3391 views)  |   In reply to mona4u

My solution is signicantly similar to what @art297 has offered.

 


data have1;
set have;
if lesnid in: ('R1','R2') then _con='TL';
else if lesnid =: 'R3' then _con='NT';
else if lesnid=:'R4' then _con='NL';
run;

data want;
do _n_=1 by 1 until (last._con);
 set have1;
  by subjid _con notsorted ;
  converted=cats(_con, put(_n_,z2.));
output;
end;
drop _con;
run;
0 Likes
Reply
mona4u
Lapis Lazuli | Level 10 mona4u
Lapis Lazuli | Level 10

Re: complicated conversion

Posted 08-28-2018 03:08 PM (3350 views)  |   In reply to Haikuo

sorry for taking the time to get back to you today I tried to validate the program with a big data set  and I found that there is issue with the conversion with some cases 

 

SUbJIDLESNIDconverted should be 
C12-123R101TL01 
C12-123R102TL02 
C12-123R201TL03 
C12-123R301NT01 
C12-123R401NL01 
C12-123R101TL01 
C12-123R102TL02 
C12-123R201TL03 
C12-123R301NT01 
C12-123R401NL01 
C12-124R201TL01 
C12-124R401NL01 
C12-124R201TL01 
C12-124R401NL01 
C12-124R201TL01 
C12-124R401NL01 
C12-124R201TL01 
C12-125R101TL01 
C12-125R101TL02 
C12-126R201TL01 
C12-126R301NT01 
C12-126R201TL01 
C12-126R301NT01 
C12-126R201TL01 
C12-126R301NT01 
C12-789R301NT01NT01
C12-789R301NT02NT01
C12-789R301NT03NT01
C12-789R301NT04NT01
C14-011R101TL01 
C14-011R201TL02 
C14-011R301NT01 
C14-011R101TL01 
C14-011R201TL02 
C14-011R301NT01 
C14-011R101TL01 
C14-011R201TL02 
C14-011R301NT01 
C14-012R101TL01 
C14-012R201TL02 
C14-012R301NT01 
C14-012R101TL01 
C14-012R201TL02 
C14-012R301NT01 
C14-012R101TL01 
C14-012R201TL02 
C14-012R301NT01 
C14-013R101TL01 
C14-013R201TL02 
C14-013R301NT01 
C14-013R101TL01 
C14-013R201TL02 
C14-013R301NT01 
C14-013R101TL01 
C14-013R201TL02 
C14-013R301NT01 
C14-013R101TL01 
C14-013R201TL02 
C14-013R301NT01 
C14-014R101TL01 
C14-014R201TL02 
C14-014R301NT01 
C14-014R101TL01 
C14-014R201TL02 
C14-014R301NT01 
C14-014R101TL01 
C14-014R201TL02 
C14-014R301NT01 
C14-014R101TL01 
C14-014R201TL02 
C14-014R301NT01 
C14-015R101TL01 
C14-015R201TL02 
C14-015R301NT01 
C14-015R101TL01 
C14-015R201TL02 
C14-015R301NT01 
C14-015R101TL01 
C14-015R201TL02 
C14-015R301NT01 
C14-016R101TL01 
C14-016R201TL02 
C14-016R301NT01 
C14-016R101TL01 
C14-016R201TL02 
C14-016R301NT01 
C14-016R101TL01 
C14-016R201TL02 
C14-016R301NT01 
C14-016R101TL01 
C14-016R201TL02 
C14-016R301NT01 
C14-017R101TL01TL01
C14-017R102TL02TL02
C14-017R201TL03TL03
C14-017R101TL04TL01
C14-017R102TL05TL02
C14-017R201TL06TL03
C14-017R101TL07TL01
C14-017R102TL08TL02
C14-017R201TL09TL03
C14-017R101TL10TL01
C14-017R102TL11TL02
C14-017R201TL12TL03

 

Can you take a look and fix the issue for me ? 

0 Likes
Reply
mona4u
Lapis Lazuli | Level 10 mona4u
Lapis Lazuli | Level 10

Re: complicated conversion

Posted 08-28-2018 04:25 PM (3340 views)  |   In reply to Haikuo
I tried the code and It didn't work with some cases
SUbJID LESNID converted Should be
C12-123 R101 TL01
C12-123 R102 TL02
C12-123 R201 TL03
C12-123 R301 NT01
C12-123 R401 NL01
C12-123 R101 TL01
C12-123 R102 TL02
C12-123 R201 TL03
C12-123 R301 NT01
C12-123 R401 NL01
C12-124 R201 TL01
C12-124 R401 NL01
C12-124 R201 TL01
C12-124 R401 NL01
C12-124 R201 TL01
C12-124 R401 NL01
C12-124 R201 TL01
C12-125 R101 TL01 TL01
C12-125 R101 TL02 TL01
C12-126 R201 TL01
C12-126 R301 NT01
C12-126 R201 TL01
C12-126 R301 NT01
C12-126 R201 TL01
C12-126 R301 NT01
C12-789 R301 NT01
C12-789 R301 NT02 NTO1
C12-789 R301 NT03 NTO1
C12-789 R301 NT04 NTO1
C14-011 R101 TL01
C14-011 R201 TL02
C14-011 R301 NT01
C14-011 R101 TL01
C14-011 R201 TL02
C14-011 R301 NT01
C14-011 R101 TL01
C14-011 R201 TL02
C14-011 R301 NT01
C14-012 R101 TL01
C14-012 R201 TL02
C14-012 R301 NT01
C14-012 R101 TL01
C14-012 R201 TL02
C14-012 R301 NT01
C14-012 R101 TL01
C14-012 R201 TL02
C14-012 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
C14-013 R101 TL01
C14-013 R201 TL02
C14-013 R301 NT01
0 Likes
Reply
art297
Opal | Level 21 art297
Opal | Level 21

Re: complicated conversion

Posted 08-28-2018 04:51 PM (3338 views)  |   In reply to mona4u

I'm confused. You replied to @Haikuo a second time, but he only posted the one set of code. You did try the code I sent about a half hour ago?

 

Art, CEO, AnalystFinder.com

 

0 Likes
Reply
ballardw
Super User ballardw
Super User

Re: complicated conversion

Posted 08-21-2018 11:38 AM (3369 views)  |   In reply to mona4u

Have you completely explained this whole process yet?

In your previous thread on the exact same issue

https://communities.sas.com/t5/General-SAS-Programming/complicated-problem/m-p/485543#M59390

 

part way through you added: Need to do R301

and an example solution was provided.

Then you added that R301 needed to map to NT instead of NL in a not precisely clear requirement:

R301 convert to NT01
R302 converts to NTO2
.. ETC.
R401 converts to NL01
R402 converts to NL02

 

So how may other values are going to need to get mapped to yet another set of sequential values? R501? R2701? To PD01? To XL01?

 

Provide the entire details or some general rule. Piecemeal lists of examples are not the same as a rule.

 

Your requirement for R3 And R4 both changing the two letter prefix values calls into question whether the process for R2 values are actually "correct" since the prefix is the same as the R1 values.

0 Likes
Reply
mona4u
Lapis Lazuli | Level 10 mona4u
Lapis Lazuli | Level 10

Re: complicated conversion

Posted 08-28-2018 03:11 PM (3346 views)  |   In reply to ballardw
I have this issue with the data set I'm basically want to get this result from the data set I posted below.

the way the variable converted came within subjid R101=TL01 and R102=TL02 .....ETC.

But the confusing part is R20X take the next number available after R10X conversion for example if TL02 already reserved the R201 will convert to TL03, R202 converts to TL04.

R301 convert to NT01

R302 converts to NTO2

.. ETC.

R401 converts to NL01
and
R402 converts to NL02.



This is the full requirement
0 Likes
Reply

sas-innovate-2026-white.png



April 27 – 30 | Gaylord Texan | Grapevine, Texas

Registration is open

Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and save with the early bird rate—just $795!

Register now

How to Concatenate Values

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

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • ‎08-20-2018 03:14 PM
  • 3426 views
  • 0 likes
  • 4 in conversation