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