topic Re: sql error in macro in SAS Programming

sql error in macro

michellel — Tue, 24 Feb 2015 01:58:01 GMT

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;

Re: sql error in macro

Jagadishkatam — Tue, 24 Feb 2015 02:21:20 GMT

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

Re: sql error in macro

Reeza — Tue, 24 Feb 2015 02:28:59 GMT

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.

Re: sql error in macro

michellel — Tue, 24 Feb 2015 02:41:58 GMT

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.

Re: sql error in macro

michellel — Tue, 24 Feb 2015 02:48:59 GMT

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.;

Re: sql error in macro

Kurt_Bremser — Tue, 24 Feb 2015 07:04:07 GMT

Add

proc contents data=MBSF_AB&i.;run;

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