Map codes between different lengths

[Ganeshk]

Hi,

I have data set

Category	Code
aaa	202020201
aaa	20202
bbb	450011111
bbb	45001
bbb	450011111
cc	466667777
cc	46666
dd	333332221
dd	33333
ee	45616
ee	232324343
ee	456166661

What i want is:

Category	Code
aaa	202020201
aaa	202020201
bbb	450011111
bbb	450011111
bbb	450011111
cc	466667777
cc	466667777
dd	333332221
dd	333332221
ee	456166661
ee	232324343
ee	456166661

Thanks,

Ganesh K

[Reeza]

 What's the logic? What's the question? 

Tip: If you took the time to initially add detail your question this would have been an answer rather than a request for further details. 

[Ganeshk]

For a category i have to convert 5 digit Code(20202) to 9 digit code (202020201).

[Reeza]

Not to repeat myself, but what's the rule/logic? 

[Ganeshk]

logic is that for each category should have same code, but in my data i have 5 length code & 9 lenght code. so i need to make them as 9 length code. 

[Reeza]

You really still didn't say how. If you can't explain it, it's unlikely you can code it. 

What if only the first 3 digits match? Is that a match?

Given your sample data this will replicate your problem. 

proc sort data=have;
by category descending code;
run;

data want;
set have;
by category descending code;
retain new_code;
if length(code)=9 then new_code=code;
run;

[Kurt_Bremser]

If the first 5 digits are unique, then use substr() to only extract those and use that for grouping/summarizing. If the first 5 digits are not unique, your issue can't be reliably solved.

[ChrisNZ]

It's difficult to guess what you are after from what you posted.You are not helping yourself here by putting so little effort into this post.

Maybe that's what you want:

data HAVE;
infile cards pad;
input CATEGORY $3. CODE : $9.;
 cards;
aaa 202020201
aaa 20202
bbb 450011111
bbb 45001
bbb 450011111
cc 466667777
cc 46666
dd 333332221
dd 33333
ee 45616
ee 232324343
ee 456166661
run;
     
proc sql;
  create table CODEMAP  as
    select unique SHORTCODE as START
                , LONGCODE  as LABEL
                , 'map'     as FMTNAME
                , 'c'       as TYPE
    from HAVE(rename=(CODE=SHORTCODE) where=(length(SHORTCODE)=5))
       , HAVE(rename=(CODE=LONGCODE ) where=(length(LONGCODE )=9))
    where  put(LONGCODE,$5.) = SHORTCODE;
quit; 

proc format cntlin=CODEMAP; 
run;

data WANT; 
  set HAVE; 
  CODE=put(CODE,$map.);
run;

or maybe not.

[Jim_G]

Expanding Reeza's  reply just a little bit

try this:

data HAVE;
infile cards pad;
input CATEGORY $3. CODE : $9. ;
code5=substr(code,1,5);
n+1;
 cards;
aaa 202020201
aaa 20202
bbb 450011111
bbb 45001
bbb 450011111
cc 466667777
cc 46666
dd 333332221
dd 33333
ee 45616
ee 232324343
ee 456166661
proc sort;  by category code5 descending code;  proc print;

data;  set; by category code5;
if first.code5 then  newcode=code;  retain newcode;
drop code  code5;
proc sort;  by n;
proc print;
run;   

data HAVE;
infile cards pad;
input CATEGORY $3. CODE : $9. ;
code5=substr(code,1,5);
n+1;
cards;
aaa 202020201
aaa 20202
bbb 450011111
bbb 45001
bbb 450011111
cc 466667777
cc 46666
dd 333332221
dd 33333
ee 45616
ee 232324343
ee 456166661
proc sort; by category code5 descending code; proc print;

data; set; by category code5;
if first.code5 then newcode=code; retain newcode;
drop code code5;
proc sort; by n;
proc print;
run;

[ballardw]

Is there any chance these values represent Zip codes??? If so there is no way without much more information to supply a vaid 9  character Zip.

It may help to provide an example of what is going to be done with this data later.