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

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

Vikachu — Fri, 04 Jul 2014 14:48:16 GMT

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;

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

stat_sas — Fri, 04 Jul 2014 15:08:07 GMT

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;

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

Vikachu — Fri, 04 Jul 2014 15:26:17 GMT

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;

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

Reeza — Fri, 04 Jul 2014 19:58:23 GMT

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.

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

Vikachu — Fri, 04 Jul 2014 20:18:43 GMT

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

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

Reeza — Fri, 04 Jul 2014 20:44:10 GMT

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

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

Patrick — Sat, 05 Jul 2014 02:19:11 GMT

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;

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

Vikachu — Mon, 07 Jul 2014 13:35:54 GMT

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

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

Vikachu — Mon, 07 Jul 2014 13:41:18 GMT

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

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

RW9 — Mon, 07 Jul 2014 13:50:36 GMT

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.

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

Vikachu — Mon, 07 Jul 2014 14:15:11 GMT

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!

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

Vikachu — Mon, 07 Jul 2014 15:25:41 GMT

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