topic Re: Map codes between different lengths in SAS Programming

Map codes between different lengths

Ganeshk — Fri, 12 May 2017 05:10:24 GMT

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

Re: How to do this?

Reeza — Fri, 12 May 2017 04:01:55 GMT

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.

Re: How to do this?

Ganeshk — Fri, 12 May 2017 04:13:02 GMT

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

Re: How to do this?

Reeza — Fri, 12 May 2017 04:18:39 GMT

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

Re: How to do this?

Ganeshk — Fri, 12 May 2017 04:52:11 GMT

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.

Re: How to do this?

Kurt_Bremser — Fri, 12 May 2017 04:56:29 GMT

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.

Re: How to do this?

Reeza — Fri, 12 May 2017 05:11:42 GMT

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;

Re: Map codes between different lengths

ChrisNZ — Fri, 12 May 2017 05:47:29 GMT

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.

Re: Map codes between different lengths

Jim_G — Fri, 12 May 2017 11:22:13 GMT

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;

Re: Map codes between different lengths

ballardw — Fri, 12 May 2017 13:59:48 GMT

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.