BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vikachu
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

11 REPLIES 11
stat_sas
Ammonite | Level 13

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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

Reeza
Super User

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

Vikachu
Calcite | Level 5


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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

Vikachu
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 17254 views
  • 0 likes
  • 5 in conversation