Translating list of codes to descriptive text

[DougCole]

I have a character field called EXPLNTN_TYP_NUM_LIST  that contains a list of ‘explanation’ code(s) separated by commas – i.e. “1,2, <x>, ..”. – that need to be translated into descriptive text(s) separated by commas – i.e. “explanation1, explanation2, <explanationx>, ..”.

To make it a little more interesting,  EXPLNTN_TYP_NUM_LIST can have up to a maximum of 9 possible explanation codes or the field could be blank/missing.

So, the question is:  how do I translate this list of codes in EXPLNTN_TYP_NUM_LIST to another field – say EXPLNTN_TYP_NUM_LIST_DESC – when I don’t know if the field will contain either a blank, 1 code or multiple codes separated by commas (up to 9 codes).   Keep in mind that if the EXPLNTN_TYP_NUM_LIST field is blank/missing, I want to return a blank/missing value as a descriptive text.

Note:  I have built a character format table using PROC FORMAT called $EXPLNTN to build out the code translations.  There are 16 possible code values.

I'm not sure how to do this using Base SAS.  Any help would be greatly appreciated.

[Reeza]

Use a loop and CATX.

1. Find the number of items, use COUNTC/COUNTW

2. Loop over items to extract each part

3. Concatenate to main string

*Make sure length is long enough to work;
length stringDesc $200.;

*counts number of words, you may need to increment by 1 or account for 0 with an IF statement;
nWords = countw(string);

do i=1 to nWords;
    x =scan(string, i);
   stringDesc = catx(", ", stringDesc, put(code, formatName.));
end;

---

@DougCole wrote:

I have a character field called EXPLNTN_TYP_NUM_LIST  that contains a list of ‘explanation’ code(s) separated by commas – i.e. “1,2, <x>, ..”. – that need to be translated into descriptive text(s) separated by commas – i.e. “explanation1, explanation2, <explanationx>, ..”.

To make it a little more interesting,  EXPLNTN_TYP_NUM_LIST can have up to a maximum of 9 possible explanation codes or the field could be blank/missing.

So, the question is:  how do I translate this list of codes in EXPLNTN_TYP_NUM_LIST to another field – say EXPLNTN_TYP_NUM_LIST_DESC – when I don’t know if the field will contain either a blank, 1 code or multiple codes separated by commas (up to 9 codes).   Keep in mind that if the EXPLNTN_TYP_NUM_LIST field is blank/missing, I want to return a blank/missing value as a descriptive text.

Note:  I have built a character format table using PROC FORMAT called $EXPLNTN to build out the code translations.  There are 16 possible code values.

I'm not sure how to do this using Base SAS.  Any help would be greatly appreciated.

---

[Tom · Accepted solution]

Just use SCAN() and CATX() functions.

data want ;
  set have;
   length new_var $400 ;
   do index=1 to countw(EXPLNTN_TYP_NUM_LIST,',');
       new_var=catx(',',new_var,put(strip(scan(EXPLNTN_TYP_NUM_LIST,index,',')),$EXPLNTN.));
   end;
run;

[ballardw]

One way, not the only way.

Proc format;
value $codelist
1 = "Meaning for Code 1"
2 = "Meaning for Code 2"
3 = "Meaning for Code 3"
4 = "Meaning for Code 4"
5 = "Meaning for Code 5"
6 = "Meaning for Code 6"
7 = "Meaning for Code 7"
8 = "Meaning for Code 8"
;
run;

Data have;
   input list $;
datalines;
1,2,3
.
4,1,3,6
;

data want;
   set have;
   length description $ 140;
   /* the 140 above is just an example for the length
      of the variable. You should set it to the sum of
      the number of characters in the 9 codes plus 8 for the 
      commas
   */
   do i= 1 to countw(list,',');
      description = catx(',',description,Put(scan(list,i,','),$codelist.) );
   end;
   drop i;
run;

Custom formats are one way to do look ups like this.

The Countw function counts "words" separated by only a comma in this case, other and multiple delimiters could be used. Then for each "word" or code value use the CATX function to concatenate the list with the Put value of the current code. CATX will use the first character in the parameters to separate the values, so a comma. SCAN extracts the given numbered list item, and then the Put applies the formatted value.

Since the Description starts out empty in each iteration of the data step using it to concatenate is an empty value and the CATX function will suppress all blanks.

If the list is missing or all blanks then Countw returns 0 and since the Do loop counter i starts at 1 it exceeds the upper bound and does not execute.

[DougCole]

Thanks for your help.  This is exactly what I needed!

Cheers.

[PGStats]

A small adaptation of @ballardw 's code will insert empty descriptions into the list:

Proc format;
value $codelist
1 = "Meaning for Code 1"
2 = "Meaning for Code 2"
3 = "Meaning for Code 3"
4 = "Meaning for Code 4"
5 = "Meaning for Code 5"
6 = "Meaning for Code 6"
7 = "Meaning for Code 7"
8 = "Meaning for Code 8"
" " = "_"
;
run;

Data have;
   input list $;
datalines;
1,2,3
.
4,1,,6
;

data want;
   set have;
   length description $ 140;
   /* the 140 above is just an example for the length
      of the variable. You should set it to the sum of
      the number of characters in the 9 codes plus 8 for the 
      commas
   */
   do i= 1 to countw(list,',',"m");
      description = catx(',',description,Put(scan(list,i,',',"m"),$codelist.) );
   end;
   description = translate(description, " ", "_");
   drop i;
run;

[DougCole]

Thanks everyone. This worked like a charm! Much appreciated.