The following columns were not found in the contributing tables: be, gc, gp, ha.

[alepage]

Hello, 

I am trying to produce a table but the received this error message: The following columns were not found in the contributing tables: be, gc, gp, ha.

This script below works fine.

 /***************************** Reading the inforcelisting file   ********************/

data inforcelisting5 ;
set dest2.inforcelisting5 (firstobs=1 obs=5);
run;

/********************** Macro function variable exist ********************************************/
%macro varexist
/*----------------------------------------------------------------------
Check for the existence of a specified variable.
----------------------------------------------------------------------*/
(ds        /* Data set name */
,var       /* Variable name */);

/*----------------------------------------------------------------------
Usage Notes:

%if %varexist(&data,NAME)
  %then %put input data set contains variable NAME;

The macro calls resolves to 0 when either the data set does not exist
or the variable is not in the specified data set.
----------------------------------------------------------------------*/

%local dsid rc ;

/*----------------------------------------------------------------------
Use SYSFUNC to execute OPEN, VARNUM, and CLOSE functions.
-----------------------------------------------------------------------*/

%let dsid = %sysfunc(open(&ds));

%if (&dsid) %then %do;
  %if %sysfunc(varnum(&dsid,&var)) %then 1;
  %else 0 ;
  %let rc = %sysfunc(close(&dsid));
%end;
%else 0;
%mend varexist;
/*************************** Mapping few variables ***********************************************/


/******************************** Creating the file info and making a join with the look  ********/

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 ',strip(cie),' eq be %then %let varname2=datechea;)',
   '%nrstr(%else %if ',strip(cie),' eq gc %then %let varname2=datechea;)',
   '%nrstr(%else %if ',strip(cie),' eq gp %then %let varname2=datechea;)',
   '%nrstr(%else %let varname2=policy_expiry_dt;)',
    '%nrstr(%if ',strip(cie),' eq be %then %let varname3=province;)',
   '%nrstr(%else %if ',strip(cie),' eq gc %then %let varname3=province;)',
   '%nrstr(%else %if ',strip(cie),' eq gp %then %let varname3=province;)',
   '%nrstr(%else %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(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(ELSE A.COMPANY_CD)',*/
/*   '%nrstr( END AS COMPANY_CD)',*/
   '%nrstr(, a.&varname2. as policy_expiry_date length=8 )',
   "from dssource.",strip(fname),"   a ;",
   '%nrstr(where strip("&varname1.") in (select agreement_nbr from lookup);)',
   "Quit;",
   '%nrstr(%mend lookuptbl;)',
   '%nrstr(%lookuptbl;)',	
   "Libname dssource clear;"
)));
run;

But when I want to add the case when condition1 else end as company_cd, I have the above mentionned error.

When the company is either be, gc, gp, the variable company_cd does not exist and I am mapping this value to either B, A or P. Otherwise the variable a.company_cd exist into the others datasets.

Does someone can help me with that issue.

[ballardw]

Show us a version of NON-macro code that works.

Then show the result generated with the macro using MPRINT.

I suspect  you are going to find a difference every where those "variables" be, gc, gp, ha are compared to a "value" or setting a value.

WHEN ',strip(cie), ' eq be

if cie is be the above becomes

WHEN be eq be

which looks like TWO uses of the variable be to me.

[Tom]

Why do you keep asking the same question?  Did you try any of the suggestions you got before?

Please explain the content of your dest2.inforcelisting5 dataset.

You seem to be taking 5 observations from that dataset and using to reference 5 other datasets. So explain the contents of the other 5 datasets.

Why are you trying to remake the same TABLE_INFO dataset 5 different times (I assume reading from 5 different input datasets)?  If it worked then at the end you would only the results from the 5th version as the previous 4 versions would have been replaced.

What is the overall goal here?

[alepage]

I am using the inforcelisting5 table because this table contains, the path (libname), engine, cie, lob, year, month and filename. So I can either map few values ex cie, lob, month, year and use the filename to read value in it such as company_cd when it is available.

[Tom]

---

@alepage wrote:

I am using the inforcelisting5 table because this table contains, the path (libname), engine, cie, lob, year, month and filename. So I can either map few values ex cie, lob, month, year and use the filename to read value in it such as company_cd when it is available.

---

What is the actual task you are trying to do?  What does the data represent?  What does the result of the ultimate SQL query represent? Why is it that you already know CIE and the other attributes but you don't know the name of the variables like AGREEMENT_NUMBER to use?

I suspect you might want to split the problem into two steps.  First find out the missing information you are using the VAREXIST() function to find.  Then once you have all the information you need you can then work on how to use it to generate the summary data you appear to want from the dataset.

It would probably be much easier to do the first part WITHOUT any macro code.  Just use the same functions you are using in your %VAREXIST() function in a datastep.  So something like this should assign an libref for each PATH/ENGINE combination and generate a DS name string in LIBREF.MEMNAME format from the libref generated for this particular PATH/ENGINE combination and this particular FNAME value.  It can then use OPEN() and VARNUM() to check which of the various variables of interest exist or not.

data inforcelisting5 ;
  set dest2.inforcelisting5 (firstobs=1 obs=5);
run;
proc sort data=inforcelisting5 ;
  by engine path fname ;
run;
data varcheck;
  set inforcelisting5;
  by engine path;
  length libref $8 ds $41;
  retain libref rc;
  if path then do;
    libref=' ';
    rc=libname(libref,path,engine,'access=readonly');
    if rc then put 'ERROR: Could not find ' path= engine= ;
  end;
  if not rc then do;
    ds=catx('.',libref,fname);
    dsid=open(ds);
    if dsid then do;
      police=0<varnum(dsid,'police');
      agreement_nbr=0<varnum(dsid,'agreement_nbr');
      datechea=0<varnum(dsid,'datechea');
      policy_expiry_dt=0<varnum(dsid,'policy_expiry_dt');
      province=0<varnum(dsid,'province');
      province_cd=0<varnum(dsid,'province_cd');
      rc=close(dsid);
    end;
    else put 'ERROR: Could not open ' ds= ;
  end;
run;

Once you have that the the logic for deciding which variable names to use in the generated could should be easier.   Try that yourself.  Just by running a data step on the data calculated above and assigning the variable names to use into actual variables (not macro variables).  That way you can print the dataset (or even browse the dataset) and check your logic until you get it doing what you want.

Once you have figured out all of the parts needed to generate the summary code it might be easiest to make one macro that takes as input the various parts of the code that varies so that all the macro needs to do is put them into the places they belong in the generated code.   This might also be a useful macro for generating the same type of summary when you just want to type in the dataset name and variable names instead of trying to calculate them.

%macro run_summary
(outds
,agreement 
,company
,policy
,lob
,year
,libref
,fname
,path
);
proc sql;
create table &outds as 
 select distinct &agreement as agreement_nbr length=20
  , &lob as lob length=4
  , &year as year length=4
  , &company as COMPANY_CD
  , &policy as policy_expiry_date
  , "&libref" as libref length=8
  , "&fname" as filename length=32
  , "&path" as path length=200
 from &libref..&fname 
 where &agreement in (select agreement_nbr from lookup)
;
quit;
%mend run_summary;

Make the names of the parameters to the macro match the names of the variables in the dataset where you have calculated what variables to use.  Then the code to generate the calls to the macro can be straight forward.  And if you use a FILE instead of CALL EXECUTE() you can even take advantage the VAR= option of the PUT statement to generate macro calls that clearly show what parameter is being passed.

filename code temp;
data _null_;
  set ds_with_varnames;
  file code;
  put '%run_summary(' outds= ',' agreement= ','  company= ',' policy= 
     ',' lob= ',' year= ',' libref= ',' fname= ',' path= ')'
  ;
run;
%include code / source2;

[alepage]

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(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( ELSE 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;

Hello, I have improve my script but it failed to properly use  the case when else statement when the variable does not exist. For example, I though that the value for company_cd could be set to 'B' for cie=be, "A' for cie eq gc , 'P' for cie eq 'gp' but this statement failed when come the time to read the else statement because the variable does not exist it failed.

So the idea is when cie = be, company_cd='B', when cie= gc then company_cd = 'A' , when cie = gp then company_cd = 'P" otherwise the value = a.company_cd . how to we do that

[Tom]

Since you know the value of CIE in the data step use an actual IF statement to test it and then just generate the SQL code you want.  So split your one CALL EXECUTE() statement into three instead of just one.

...
call execute(....);
if cie='be'  then call execute('"B"');
else if cie='qc' then call execute('"A"');
else if cie='gp' then call execute('"P"');
else call execute('a.company_cd');
call execute(...);
...