topic Re: complicated conversion in SAS Programming

complicated conversion

mona4u — Mon, 20 Aug 2018 19:16:47 GMT

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

SUBJID	LESNID	Converted
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
C14-062	R101	TL01
C14-062	R201	TL02
C14-062	R301	NT01
C14-062	R401	NL01
C14-062	R402	NL02
C14-062	R101	TL01
C14-062	R201	TL02
C14-062	R301	NT01
C14-062	R401	NL01
C14-062	R402	NL02
C14-062	R101	TL01
C14-062	R201	TL02
C14-062	R301	NT01
C14-062	R401	NL01
C14-062	R402	NL02
C14-062	R101	TL01
C14-062	R201	TL02
C14-062	R301	NT01

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;

Re: complicated conversion

art297 — Mon, 20 Aug 2018 20:20:33 GMT

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

Re: complicated conversion

Haikuo — Mon, 20 Aug 2018 20:29:55 GMT

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;

Re: complicated conversion

ballardw — Tue, 21 Aug 2018 15:38:18 GMT

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.

Re: complicated conversion

mona4u — Tue, 28 Aug 2018 19:08:09 GMT

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 ?

Re: complicated conversion

mona4u — Tue, 28 Aug 2018 19:11:34 GMT

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

Re: complicated conversion

mona4u — Tue, 28 Aug 2018 19:12:25 GMT

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 ?

Re: complicated conversion

art297 — Tue, 28 Aug 2018 20:17:15 GMT

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

Re: complicated conversion

mona4u — Tue, 28 Aug 2018 20:25:52 GMT

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

Re: complicated conversion

art297 — Tue, 28 Aug 2018 20:51:11 GMT

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

Re: complicated conversion

mona4u — Tue, 28 Aug 2018 21:39:15 GMT

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;

Re: complicated conversion

art297 — Tue, 28 Aug 2018 21:51:56 GMT

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