sql error in macro

[michellel]

Hi, I have a question as below. Thanks a lot in advance for your answer.

I want to see the distinct count and total count.

When I tried codes as blow for just one year 2010, it is fine to get result.

proc sql;

select count(distinct BE_ID) as Nbe_uni,

          count(*) as Nbe

from mbsf_ab2010;

quit;

When I tried to use macro to get results for 10 years, it shows error for every year as blow, but there is variable BE_ID for every year and the files are located in work folder.

%macro uni;

%do i=2001 %to 2010;

proc sql;

select count(distinct BE_ID) as Nbe_uni,

          count(*) as Nbe

from MBSF_AB&i.;

ERROR: The following columns were not found in the contributing tables: BE_ID.

quit;

%end;

%mend;

%uni;

[Jagadishkatam]

the code seems correct. could you please check the file individually again  to make sure there is BE_ID variable in all the files. Alternative to confirm the same , please try to execute the code this way and see if it works without error.

%macro uni;

%do i=2010 %to 2010;

proc sql;

select count(distinct BE_ID) as Nbe_uni,

          count(*) as Nbe

from MBSF_AB&i.;

quit;

%end;

%mend;

%uni;

Thanks,

Jag

[michellel]

Thanks Jagadishkatam. I have checked the variable BE_ID for ten files. They are all there, with the same name. I did run the code as you listed, but still got that ERROR.

[Reeza]

Run the code with the following options:

options mprint symbolgen;

*code;

Take a look at the output, it should help you debug the code. If you still can't find the error, post the log with the following options from a single iteration with the error.

[michellel]

Thanks Reeza. I will try the options tomorrow.

Sorry I could not post the log. I did not have any access to any other website when I accessed into virtual server, as the data sets are very confidential. There is no output, as there is ERROR. In log, it shows that ERROR(The following columns were not found in the contributing tables: BE_ID.) notice under the command

select count(distinct BE_ID) as Nbe_uni,

          count(*) as Nbe

from MBSF_AB&i.;

[Kurt_Bremser]

Add

proc contents data=MBSF_AB&i.;run;

to your macro loop and look at the output(s) of that.