case when else end as varname is not working properly when the variable does not exist

[alepage]

Hello, 

I am trying to create  new variable using a case when else end as company_cd but it does not work as expected.

data _null_;
   set inforcelisting5;
   call execute
   (compbl(cat(
   "Libname dssource ", engine,'"',path,'";',
   '%nrstr(%macro lookuptbl;)',
   /******************** Mapping few variables *************************************/
   '%nrstr(%global varname1 varname2 varname3 ;)',
   '%nrstr(%if %varexist(dssource.',strip(fname),',police) %then %let varname1=police;)',
   '%nrstr(%else %if %varexist(dssource.',strip(fname),',agreement_nbr) %then %let varname1=agreement_nbr;)',
   '%nrstr(%if %varexist(dssource.',strip(fname),',police) %then %let varname2=datechea;)',
   '%nrstr(%else %if %varexist(dssource.',strip(fname),',agreement_nbr) %then %let varname2=policy_expiry_dt;)',
   '%nrstr(%if %varexist(dssource.',strip(fname),',province) %then %let varname3=province;)',
   '%nrstr(%else %if %varexist(dssource.',strip(fname),',risk_province_cd) %then %let varname3=risk_province_cd;)',
   '%nrstr(%else %if %varexist(dssource.',strip(fname),',policy_province_cd) %then %let varname3=policy_province_cd;)',
   '%nrstr(%else %if %varexist(dssource.',strip(fname),',province_cd) %then %let varname3=province_cd;)',   
   '%nrstr(%put &=varname1 &=varname2 &=varname3;)', 
   /************************ End of Mapping few variables ***************************/
   "proc sql;",
   "create table table_info as ",
   '%nrstr(Select distinct strip(a.&varname1.) as agreement_nbr length=20 )',
   ',"',strip(cie), '"  as cie length=2',
   ',"',strip(lob), '"  as lob length=4',
   ',"',strip(year), '"  as year length=4',
   ',"',strip(fname), '"  as filename length=25',
   ',"',strip(path), '"  as libname length=100 ',   
   '%nrstr(, CASE)',
   '%nrstr(     WHEN "',strip(cie),'" eq "be" then "B" )',
   '%nrstr(     WHEN "',strip(cie),'" eq "gc" then "A" )',
   '%nrstr(     WHEN "',strip(cie),'" eq "gp" then "P" )',
   '%nrstr(     WHEN "',strip(cie),'" not in ("be","gc","gp") then a.company_cd )',
   '%nrstr( END AS COMPANY_CD )',
   '%nrstr(from dssource.',strip(fname),' as  a ; )', 
    "Quit;",
   '%nrstr(%mend lookuptbl;)',
   '%nrstr(%lookuptbl;)',	
   "Libname dssource clear;"
)));
run;

proc sql;

select

case

     when cie eq 'be' then "B"

     when cie eq 'gc' then "A"

    When cie eq 'gp' then "P"

    when cie not in ('be','gc','gp') then a.company_cd

end as company_cd

from ...

innner join...

on()...

Quit;

This script should map company_cd properly it does work properly.  Does someone has an idea how to solve that issue

NOTE: CALL EXECUTE generated line.
1 + Libname dssource base"/dwh_actuariat/sasdata/sas2002/be/habi/bel.prod1000.sah1stat.dec2002.data/ ";
NOTE: Libref DSSOURCE was successfully assigned as follows:
Engine: BASE
Physical Name: /dwh_actuariat/sasdata/sas2002/be/habi/bel.prod1000.sah1stat.dec2002.data/
1 + %macro
lookuptbl;%global varname1 varname2 varname3 ;%if %varexist(dssource.polices,police) %then %let varname1=police;%else %if %varexist
2 +(dssource.polices,agreement_nbr) %then %let varname1=agreement_nbr;%if %varexist(dssource.polices,police) %then %let
varname2=datechea;%else %if %varexist(dssource.polices,agreement_nbr) %then %let varname2=policy_expiry_dt;%if %varexist
3 +(dssource.polices,province) %then %let varname3=province;%else %if %varexist(dssource.polices,risk_province_cd) %then
%let varname3=risk_province_cd;%else %if %varexist(dssource.polices,policy_province_cd) %then %let
varname3=policy_province_cd;%else %if
4 + %varexist(dssource.polices,province_cd) %then %let varname3=province_cd;%put &=varname1 &=varname2 &=varname3;proc
sql;create table table_info as Select distinct strip(a.&varname1.) as agreement_nbr length=20 ,"be" as cie length=2,"habi" as lob
5 + length=4,"2002" as year length=4,"polices" as filename
length=25,"/dwh_actuariat/sasdata/sas2002/be/habi/bel.prod1000.sah1stat.dec2002.data/" as libname length=100 , CASE WHEN "be" eq
"be" then "B" WHEN "be" eq "gc" then "A" WHEN "be" eq "gp" then "P"
6 + WHEN "be" not in ("be","gc","gp") then a.company_cd END AS COMPANY_CD from dssource.polices as a ; Quit;%mend
lookuptbl;%lookuptbl;Libname dssource clear;
VARNAME1=police VARNAME2=datechea VARNAME3=province
ERROR: Column company_cd could not be found in the table/view identified with the correlation name A.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

NOTE: Libref DSSOURCE has been deassigned.

[Tom · Accepted solution]

Test the value of CIE in the DATA STEP, not in the SQL code.

Use the data step to only generate the SQL code that includes A.COMPANY_CD when it exists.

[alepage]

I will test that latter during the evening. Thanks

[ballardw]

With so many "variable does not exist" errors this is starting to sound like you really don't know your data sets.

Is there some terribly important reason that your aren't use an ELSE in  that CASE:

case
     when cie eq 'be' then "B"
     when cie eq 'gc' then "A"
    When cie eq 'gp' then "P"
    else a.company_cd
end as company_cd

Would certainly be  a lot shorter than saying "compare this value to the already compared list and if it is not in that list then do this assignment"

[alepage]

when cie in (be, gc,gp) the variable company_cd does not exist and I want to map this variable respectively to B, A, P.

When cie is not in (be,gc,gp) the variable company_cd exits and I want to use this value.

[ballardw]

---

@alepage wrote:

when cie in (be, gc,gp) the variable company_cd does not exist and I want to map this variable respectively to B, A, P.

When cie is not in (be,gc,gp) the variable company_cd exits and I want to use this value.

---

That appears not to be what you wrote in the code and is why you get

ERROR: Column company_cd could not be found in the table/view identified with the correlation name A.

You cannot use a reference to a variable if it does not exist. Period.

So you need something OUTSIDE of the Proc SQL that is checking the value of cie and when it is one of those value you cannot have the CODE with that a.company_cd name referenced.

So, describe what that SQL step is supposed to actually do. In some detail.

WHEN clauses assume the variable exists. It appears that is not the actual case from that comment so that SQl really needs a clear cut description of what it is supposed to do. Just like the rest of this code.

Or better, what is the whole process supposed to do and what does that source data set contain.

I still don't see any answer to why you though CALL EXECUTE and making multiple versions of the macro was needed in the first place.

[Quentin]

This looks like an unusually complex approach.  I enjoy the macro language, and CALL EXECUTE, but I think it's rare to use CALL EXECUTE to execute the code to define a macro. In this case, when the DATA step runs it will generate code to create the macro LOOKUPTBL.  The macro itself has no  parameters but in a sense it's dynamic because you're generating the macro source code dynamically.  If there are 10 records in work.inforcelisting5 you'll create the macro LOOKUPTBL 10 times, and each time it could have a different definition. 

I would think this would be easier to design, debug, and maintain if you just define a macro with a few parameters:

%macro lookuptbl (data=,varname1=, varname2=, varname3=);
  ...   
%mend;

With that approach you could still use CALL EXECUTE to generate the macro calls, and you could use if VAREXIST to decide which variable names to pass into each macro call.

Or if you want to leave the variable exists tests in the macro, you could do it like:

%macro lookuptbl(data);

%local varname1 varname2 varname3;

%if %varexist(&data,police) %then %let varname1=police;
%else %if %varexist(&data,agreement_nbr) %then %let varname1=agreement_nbr;
*...;

%put &=varname1 &=varname2 &=varname3;
   
proc sql;
  create table table_info as
  select distinct
    strip(a.&varname1.) as agreement_nbr length=20
   ,strip(cie) as cie length=2',
   /* ... */
  ;
quit;

%mend lookuptbl;

The idea of dynamically generating macro definitions is kind of scary to me, but I'm sure people do it. Usually macros are dynamic enough, especially when paired with using CALL EXECUTE or similar to generate macro calls.