DATA Step, Macro, Functions and more

Map codes between different lengths

Reply
Contributor
Posts: 29

Map codes between different lengths

[ Edited ]

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

Super User
Posts: 19,822

Re: How to do this?

 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. 

Contributor
Posts: 29

Re: How to do this?

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

 

Super User
Posts: 19,822

Re: How to do this?

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

 

Contributor
Posts: 29

Re: How to do this?

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. 

Super User
Posts: 19,822

Re: How to do this?

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;
Super User
Posts: 7,809

Re: How to do this?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 1,760

Re: Map codes between different lengths

 

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.

Frequent Contributor
Posts: 95

Re: Map codes between different lengths

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;

Super User
Posts: 11,343

Re: Map codes between different lengths

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.

Ask a Question
Discussion stats
  • 9 replies
  • 174 views
  • 0 likes
  • 6 in conversation