ERROR: Column agreement_nbr could not be found in the table/view identified with the correlation nam

[alepage]

How to solve that issue ?

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 ",
   "Select distinct a.&varname1. as agreement_nbr length=20 ",
   "from dssource.",strip(fname),"   a ;",
   "quit;",
   '%nrstr(%mend lookuptbl;)',
   '%nrstr(%lookuptbl;)',	
   "Libname dssource clear;"
)));
run;

NOTE: CALL EXECUTE generated line.
1 + Libname dssource base"/dwh_actuariat/sasdata/sas2002/be/habi/bel.prod1000.sah1stat.dec2002.data/ ";
NOTE: Libref DSSOURCE refers to the same physical library as SRC1.
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 be eq be %then %let varname2=datechea;%else %if
be eq gc %then %let varname2=datechea;%else %if be eq gp %then %let varname2=datechea;%else %let varname2=policy_expiry_dt;%if be
eq be
3 + %then %let varname3=province;%else %if be eq gc %then %let varname3=province;%else %if be eq gp %then %let
varname3=province;%else %let varname3=province_cd;%put &=varname1 &=varname2 &=varname3;proc sql;create table table_info as Select
distinct
4 + a.agreement_nbr as agreement_nbr length=20 from dssource.polices a ;quit;%mend lookuptbl;%lookuptbl;Libname dssource
clear;
VARNAME1=police VARNAME2=datechea VARNAME3=province
ERROR: Column agreement_nbr could not be found in the table/view identified with the correlation name A.
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.

[ballardw]

Why do you think you need to use Call Execut to create multiple versions of the same macro?

If you have multiple observations in that data set you are creating different versions of the macro and I suspect you are running into a timing a issue of which version is the one that is used.

Also you may want to turn on OPTIONS MPRINT to see if the macro code is generating the statements you thing it should.  Macro variables that exist int the Lookuptbl macro may be getting changed by your Varexist macro if the same variable name is used in the code of Varexist. If that is happening then you need to explicitly set those variables as %local in Varexist.

Pretty clear:  dssource.polices data set does not have a variable named agreement_nbr at the time that particular proc sql executed.

If your %varexist macro is supposed to check for the existence of a variable in data set the bit that Varname1=police means that this "%else %if" never executed

%else %if %varexist (dssource.polices,agreement_nbr) %then %let varname1=agreement_nbr;

And that somewhere your SQL is using the wrong value of Varname1 which the %put shows as Police but the sql used agreement_nbr.

[PaigeMiller]

From now on please include logs in the text box (use the </> icon), like this:

VARNAME1=police VARNAME2=datechea VARNAME3=province
ERROR: Column agreement_nbr could not be found in the table/view identified with the correlation name A.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

There is no such variable agreement_nbr in the data set. You either have the wrong name or the wrong data set (or both). Since we don't have your data sets, you need to investigate this.

When debugging code with macro variables, it would be helpful to first turn on the macro debugging options by running this line of code:

options mprint symbolgen;

and then re-run your code and show us the log for the PROC SQL (if you can't figure it out).

[Tom]

First thing is there is NO need to redefine the macro over and over.  Just make it an test it to make sure it works.  Then your code generation step can use it with confidence.

Here is a simplified example (I have not idea what you intend to do with that CIE variable and how it is supposed to impact the choice of variable name to use).

%macro lookupname(dsname);
%if not %symexist(varname1) %then %global varname1;
%let varname1="No variable found";
%if %varexist(&dsname,police) %then %let varname1=police;
%else %if %varexist(&dsname,agreement_nbr) %then %let varname1=agreement_nbr;
%mend;

Let's try it on SASHELP.CLASS and see if it works.

8    %lookupname(sashelp.class);
9    %put &=varname1;
VARNAME1="No variable found"

Now let's try it on a dataset that does have POLICE variable.

11   data class;
12     set sashelp.class;
13     police=sex;
14   run;

NOTE: The data set WORK.CLASS has 19 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


15
16   %lookupname(class);
17   %put &=varname1;
VARNAME1=police

I find it is MUCH easier to just use the PUT statement to generate the code to a FILE instead of using CALL EXECUTE().  

filename code temp;
data _null_;
  file code;
  set .... ;
  put ..... ;
run;

That will let you take advantage of the power of the PUT statement (and SAS formats).  You won't have to worry about the macro processor interpreting the text before it can get executed (so no more %NRSTR() functions needed).  But it also means you have something you can LOOK and make sure the logic for generating the code is working.

data _null_;
  infile code;
  input;
  put _infile_;
run;

So let's try your example program.  First let's make a driver dataset that will point to the CLASS dataset we made above that does have a POLICE variable.

data inforcelisting5;
  length fname $32 cie $8 engine $10 path $200;
  fname='class';
  path=pathname('work');
  output;
run;

So let's just make a temporary code file.

filename code temp;

data _null_;
  file code ;
  set inforcelisting5;
  put 'Libname dssource ' engine path :$quote. ';'
    / '%lookupname(dssource.' fname ');'
    / '%put &=varname1;'
    / 'proc sql;'
    / 'create table table_info as'
    / '  select distinct &varname1. as agreement_nbr length=20'
    / '  from dssource.' fname 'a'
    / ';'
    / 'quit;'
    / 'libname dssource clear;'
  ;
run;

Which will generate this code.

Libname dssource   "..." ;
%lookupname(dssource.class );
%put &=varname1;
proc sql;
create table table_info as
  select distinct &varname1. as agreement_nbr length=20
  from dssource.class a
;
quit;
libname dssource clear;

So let's run it.

850   %include code / source2;
NOTE: %INCLUDE (level 1) file CODE is (system-specific pathname).
851  +Libname dssource   "..." ;
NOTE: Libref DSSOURCE was successfully assigned as follows:
      Engine:        V9
      Physical Name: (system-specific file/path name)
852  +%lookupname(dssource.class );
853  +%put &=varname1;
VARNAME1=police
854  +proc sql;
855  +create table table_info as
856  +  select distinct &varname1. as agreement_nbr length=20
857  +  from dssource.class a
858  +;
NOTE: Table WORK.TABLE_INFO created, with 2 rows and 1 columns.

859  +quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


860  +libname dssource clear;
NOTE: Libref DSSOURCE has been deassigned.
NOTE: %INCLUDE (level 1) ending.

And here is our resulting dataset