BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser101
Obsidian | Level 7

i am rewriting the question. apologize for creating confusion

 

i have following lookup table that as code and description. i have approximately 80 rows in lookup table.

 

 CodeDescription
1abc         this is for value a
2bgcbs      contact customer
3jfhfg         wait for response
4shdg        should be ready
5mimade indoor
80ninot indoor

 

 i have created Combined Codes column using array. i am trying to convert Combined Codes column like Combined description by applying format from above lookup table. Combined Codes are seperated by ' : ' .

 

Customer nameabc_checkbgcbs _checkjfhfg_check        shdg_check        mi_checkni_checkCombined CodesCombined Description
aabc          jfhfg         mi abc  : jfhfg : mithis is for value a :   wait for response :  made indoor
b   shdg         nishdg : nishould be ready :  not indoor
c bgcbs       mi bgcbs : mi contact customer : made indoor
dabc          jfhfg           abc  : jfhfg        this is for value a :   wait for response
e bgcbs      shdg        mi bgcbs : shdg : mi contact customer : should be ready : made indoor
f   shdg         nishdg : nishould be ready :  not indoor

 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@ballardw solution is correct, create the format from table 1 using CTNLIN table with PROC FORMAT.

 

See example 8 on page 10 here:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

 

View solution in original post

6 REPLIES 6
ballardw
Super User

This may give a starting point:

proc format library=work;
value $abc
'abc'=' this is for value a '
'bgcbs'=' contact customer '
'jfhfg'=' wait for response '
'shdg'=' should be ready '
;
run;

data example;
   str = 'abc:abc:bgcbs:jfhfg:shdg';
   length newstr $ 200;
   newstr=str;
   do i = 1 to (countw(str,':'));
      newstr = tranwrd(newstr,scan(newstr,i,':'),put(scan(newstr,i,':'),$abc.));
   end;
   drop i;
run;

Note that you will need to determine a maximum length for the target variable newstr to allow for the total increased length of the inserted characters. If you starting strings have 15 codes and may be up to 20 letters in the value then including : and spaces you'll likely want it to be around 350 or so characters.

 

 

Multilable isn't going to help here. It might if you pulled each code into a separate variable but I'm having a hard time seeing what that may be without an actual multi label value example.

sasuser101
Obsidian | Level 7

Thank you for response. i apologise i forgot to mention i have upto 80 columns with codes and description. how would it work in that case when creating format ?

ballardw
Super User

@sasuser101 wrote:

Thank you for response. i apologise i forgot to mention i have upto 80 columns with codes and description. how would it work in that case when creating format ?


You might have to provide a bit more detail. Are you saying that each column within that combined string would have a different format needed?

Or just that you have a lot of codes that happen to be in a file with a layout like this:

 

Code  <code meaning>    code <code meaning

 

If you need to apply different formats/rules to different parts of the combined string then thats another issue and possibly very complex.

 

 

 

sasuser101
Obsidian | Level 7

I have updated the post. Sorry for confusion.

Reeza
Super User

@ballardw solution is correct, create the format from table 1 using CTNLIN table with PROC FORMAT.

 

See example 8 on page 10 here:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

 

Reeza
Super User

You can create a format from a dataset, you don't need to write it out manually.  Search for a paper titled 'Not just another pretty face' that has a great walk through of PROC FORMAT with examples. 

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
  • 6 replies
  • 609 views
  • 0 likes
  • 3 in conversation