topic Case Statement from a Macro: Possible? in SAS Programming

Case Statement from a Macro: Possible?

PhatRam33 — Fri, 04 May 2018 14:58:44 GMT

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.

Re: Case Statement from a Macro: Possible?

Reeza — Fri, 04 May 2018 15:05:51 GMT

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.

Re: Case Statement from a Macro: Possible?

RW9 — Fri, 04 May 2018 15:23:30 GMT

"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.

Re: Case Statement from a Macro: Possible?

ballardw — Fri, 04 May 2018 17:06:26 GMT

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.