SAS Format from dataset seperated by character

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

SAS Format from dataset seperated by character

[ Edited ]

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


Accepted Solutions
Solution
‎04-25-2017 06:59 PM
Super User
Posts: 19,105

Re: SAS Format from dataset seperated by character

@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


All Replies
Super User
Posts: 11,121

Re: SAS Format from dataset seperated by character

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.

Contributor
Posts: 29

Re: SAS Format from dataset seperated by character

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 ?

Super User
Posts: 11,121

Re: SAS Format from dataset seperated by character


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.

 

 

 

Contributor
Posts: 29

Re: SAS Format from dataset seperated by character

I have updated the post. Sorry for confusion.

Solution
‎04-25-2017 06:59 PM
Super User
Posts: 19,105

Re: SAS Format from dataset seperated by character

@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

 

Super User
Posts: 19,105

Re: SAS Format from dataset seperated by character

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 168 views
  • 0 likes
  • 3 in conversation