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.
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.
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.
"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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.