HELP - Proc sql error: The following columns were not found in the contributing tables..

[Vikachu]

Hi all,

I've been getting the following error :" ERROR: The following columns were not found in the contributing tables: ..." when I run a query to create a table of dupicated ids.   The thing is I haven't changed the code, and it worked before so I don't understand why I'm getting this error message and am at a lost at how to resolve it.  If anyone can help that would be appreciated. Thanks.

Below is my code:

%let main= my dataset;

%let id= my id;

%let step= my step;

%macro dups_table (dsin=&main);
proc sql;
    create table dups_&&step as
    select *, count(*) as count
    from &dsin
     where &id ne 0
group by &step
    having count(*) > 1  ;
quit;

proc print data=dups_&&step;
title "Matched by &step.";
run;
%mend dups_table;
%dups_table;

[stat_sas]

Not sure, why there is a space between  my dataset, my id, my step

%let main= my dataset;

%let id= my id;

%let step= my step;

[Vikachu]

sorry, i just removed the original variables and typed freehand as I have a total of 25 steps with different names.  It should be:

%let main=my_dataset;

%let step=my_step;

%let id=my_id;

[Reeza]

Post a proc contents from your data set, most likely the error is correct. If you run it with different data sets and make a mistake with one of the vars this will happen.

[Vikachu]

The thing is when I do a proc content of the dataset in question, the variable is there. 

[Reeza]

Post full code and log, including results from proc content.

[Vikachu]

My apologies for the late reply, attached are the files you requested.

[RW9 · Accepted solution]

The easy way of looking at it, copy and paste the code to replace macro variables:

%macro dups_table (dsin=&main);

proc sql;

    create table dups_step2 as

    select *, count(*) as count

    from step2 (keep= WNV_ID ID_S2 Specimen__ Patient_ID Patient_Given_Names Patient_Date_of_Birth Patient_Age Patient_Gender

Patient_City Patient_City_Desc Patient_Health_No )

where ID_S2 ne 0

group by step2

    having count(*) > 1  ;

quit;

As you can see above your keep= statement does not mention  the variable STEP2, hence it is not present to the SQL statement however you are trying to group your data by that STEP2 variable, hence an error.

[Vikachu]

Thanks RW9!  That was what I figured out this morning...

[Vikachu]

Thanks Reeza,stat@sas, and Patrick for your help! I just wanted to let you know I figured it out my looking over the files you requested.  I got the error because I dropped the variable in question in a keep statement...I guess I needed a weekend away from coding to see clearly. =P

Cheers!

[Patrick]

Your code as such works as below example using "sashelp.class" demonstrates. Not sure why you use a double ampersand in "&&step". A single ampersand would be sufficient in the code you've posted.

If your code is not working then either your variable doesn't exist OR it doesn't follow the SAS naming standards. If if's a non complying variable name then pass it to the program as SAS name literal in the form: "my variable"n

I've added such quoting already to the code below.

%let main=sashelp.class;

%let step=height;

%let id=age;

%macro dups_table (dsin=&main);

  proc sql;

    create table dups_&step as

      select *, count(*) as count

        from &dsin

          where "&id"n ne 0

            group by "&step"n

              having count(*) > 1;

  quit;

  proc print data=dups_&step;

    title "Matched by &step.";

  run;

%mend dups_table;

%dups_table;

[Vikachu]

Thanks for the tips Patrick.  Unfortuately, I am still getting the error message. =(