BookmarkSubscribeRSS Feed
Ganeshk
Obsidian | Level 7

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

9 REPLIES 9
Reeza
Super User

 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
Obsidian | Level 7

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

 

Reeza
Super User

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

 

Ganeshk
Obsidian | Level 7

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
Super User

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
Super User

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
Tourmaline | Level 20

 

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
Pyrite | Level 9

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 991 views
  • 0 likes
  • 6 in conversation