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