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"
;
run;
%mend run_procformat;
%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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.