SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Occasional Contributor
Posts: 7

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

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;

Trusted Advisor
Posts: 1,204

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

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;

Occasional Contributor
Posts: 7

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

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;

Super User
Posts: 17,776

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

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.

Occasional Contributor
Posts: 7

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

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

Super User
Posts: 17,776

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

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

Occasional Contributor
Posts: 7

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


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

Attachment
Attachment
Attachment
Super User
Super User
Posts: 7,392

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

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.

Occasional Contributor
Posts: 7

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

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

Occasional Contributor
Posts: 7

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

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!

Respected Advisor
Posts: 3,887

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

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;

Occasional Contributor
Posts: 7

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

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

Ask a Question
Discussion stats
  • 11 replies
  • 4688 views
  • 0 likes
  • 5 in conversation