topic Re: Translating list of codes to descriptive text in SAS Programming

Translating list of codes to descriptive text

DougCole — Fri, 08 Jan 2021 23:22:28 GMT

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.

Re: Translating list of codes to descriptive text

Reeza — Sat, 09 Jan 2021 00:12:57 GMT

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.

Re: Translating list of codes to descriptive text

Tom — Sat, 09 Jan 2021 00:13:33 GMT

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;

Re: Translating list of codes to descriptive text

ballardw — Sat, 09 Jan 2021 00:18:03 GMT

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.

Re: Translating list of codes to descriptive text

PGStats — Sat, 09 Jan 2021 05:05:27 GMT

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;

Re: Translating list of codes to descriptive text

DougCole — Mon, 11 Jan 2021 22:06:00 GMT

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

Re: Translating list of codes to descriptive text

DougCole — Tue, 12 Jan 2021 14:13:25 GMT

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

Cheers.