If you import that look up table you can create a format from a data set as well using the CNTLIN option.
@Sascoder wrote:
I often use a mix of excel and sas to do something like this.
For example, I generate the unique categories with sas (like with proc freq) then copy them to a column in excel, then in second column, I enter the new value. Then in third column, I create the sas code statement, referring to the excel cells. Excel is very easy to copy/paste cells so this is pretty efficient. For example (and need to pay attention to the quotes so the text is properly quoted in the code):
="if cause_of_injury in (' " & a1 & " ') then cause=' " & b1 " ';' "
Then copy all rows back to SAS code. You can even store in a separate .sas file and use the %include statement which helps keep code cleaner.
Sometimes I create a giant proc format statement with this same method, and then use PUT statement to assign the new variable.
For example, in excel:
=" ' " & a1 & " ' = ' " & b1
Then in the sas code: proc format;
value $causefmt;
[copy all the lines from excel]
data tmp;
set data;
cause=put(cause_of_injury,$causefmt.);
run;
... View more
