Case Statement from a Macro: Possible?

[PhatRam33]

I have a case statement that will need to be used in multiple PROC SQL procedures and wanted to see if this can be stored in a macro similar to how it can be done with an IN statement.

Case Statement Sample:

,case

when t1.FIELD_NM IN ('EAGLE') THEN 'BIRD'

when t1.FIELD_NM IN ('SALMON') THEN 'FISH'

else 'NA' end as CLASS_NM

Can it be done doing something like this?

%LET CASETEST =

when t1.FIELD_NM IN ('EAGLE') THEN 'BIRD'

when t1.FIELD_NM IN ('SALMON') THEN 'FISH'

;

Then in PROC SQL Call as (probably have the syntax wrong):

,case

&CASETEST

else 'NA' end as CLASS_NM

Thanks in advance.

[Reeza]

You could but that seems like a horrible design. 

If you're doing look ups with the lookup tables known, I would suggest using a custom user defined format instead. Much easier to manage in the long run. 

---

@PhatRam33 wrote:

I have a case statement that will need to be used in multiple PROC SQL procedures and wanted to see if this can be stored in a macro similar to how it can be done with an IN statement.

Case Statement Sample:

,case

when t1.FIELD_NM IN ('EAGLE') THEN 'BIRD'

when t1.FIELD_NM IN ('SALMON') THEN 'FISH'

else 'NA' end as CLASS_NM

Can it be done doing something like this?

%LET CASETEST =

when t1.FIELD_NM IN ('EAGLE') THEN 'BIRD'

when t1.FIELD_NM IN ('SALMON') THEN 'FISH'

;

Then in PROC SQL Call as (probably have the syntax wrong):

,case

&CASETEST

else 'NA' end as CLASS_NM

Thanks in advance.

---

[RW9]

"will need to be used in multiple PROC SQL procedures" - then put the data into a dataset and use merging, or lookups.  I.e. a table:

FIELD_NAME  CATEGORY

EAGLE             BIRD

SALMON          FISH

...

Then merge that on by FIELD_NAME.

[ballardw · Accepted solution]

Here is a very brief example implementing @Reeza's format approach.

Proc format library=work;
value $lookup
'EAGLE', 'SPARROW','OWL'='BIRD'
'SALMON','TROUT','BASS' ='FISH'
;
RUN;

DATA work.have;
    input animal $;
datalines;
EAGLE
SPARROW
OWL
SALMON
TROUT
BASS 
;
run;

proc sql;
   create table want as
   select animal, put(animal,$lookup.) as Class_nm
   from work.have
   ;
quit;

However it may not be needed to add a variable at all. SAS will recognize the formatted value for most analysis procedures.

proc freq data=work.have;
   tables animal;
   format animal $lookup.;
run;

Of course in most reports you could also assign a format and get the desired display value from the variable using a format.

If the list is to be used frequently then creating the format(s) and placing them in a permanent library with your SAS FMTSEARCH option set to look there means that the format code need not be run every time it is used and can be share by others. If you use this approach I suggest using the CNTLOUT option in Proc Format to create a data set that can be used to recreate the formats in case someone "misplaces" the format source code.