DATA Step, Macro, Functions and more

Elegant Proc Format Using Macro Variables

Posts: 1

Elegant Proc Format Using Macro Variables

I work in the healthcare field and in my SAS code I frequently am looking at codes for different medications.  I usually create a macro variable like the following:

%let codes_ndc_lipitor = '0071015523' '0071015534' '0071015540';

Codes are stored as character variables in the SAS data sets I work with.  99% of the time I just include the above macro variable in an "IN" statement to subset out my data like this:

where (ndccode in (&codes_ndc_lipitor.));

This time I decided to get fancy and use the SAS macro language to create a format that includes the codes in the final formatting without quotes and with commas, like this:

Lipitor (0071015523,0071015534,0071015540)

So I need to create a line in Proc Format like the following:

value '0071015523','0071015534','0071015540' = 'Lipitor (0071015523,0071015534,0071015540)'

I would like to use the SAS macro language to do this because I may be looking at multiple medications and want any updates to the above drug list to change with that single macro variable that the top of my program.  I can get 99% of the way there but just cannot finish the Proc Format off.  Here is what I have so far:

%macro run_procformat();

proc format;
     value $ndc_fmt

                 %sysfunc(translate(&codes_ndc_lipitor.,%str(,),%str( ))) = %sysfunc(cat(%str(%'Lipitor %(),%sysfunc(translate(%sysfunc(compress(&codes_ndc_lipitor.,%str(%'))),%str(,),%str( ))),%str(%)%')))

               other = "Other NDC"

%mend run_procformat;


The left-hand side of the above VALUE statement (before the equals sign) in the above Proc Format works fine; that list of codes is properly formatted and ready to go.  The right-hand side (after the equals sign) is causing me issues.  I have tried with and without the CAT statement and received errors both ways. What I think is going on from the log is SAS is trying to help me out by inserting a quotation mark to close off that first missing one [the %str(%'Lipitor %(] when it would all work out if it just processed the whole thing.  SAS treats the double pipe (||) concatenate operator as text in the format so that has not worked.

I can get this to work if I use a DATA _NULL_ SAS data set and create a macro variable to handle the right-hand side above but I wanted to know if there is a way to do it all in the Proc Format without relying on another step.  If I can get this close, I believe it is possible and I am just missing something.  My apologies if this question has already been answered on the forum but I have already spent a couple of hours looking for solutions and do not have the patience to hunt around any more.  Thank you.

Ask a Question
Discussion stats
  • 0 replies
  • 1 in conversation