DATA Step, Macro, Functions and more

sql error in macro

Reply
Contributor
Posts: 66

sql error in macro

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;

Trusted Advisor
Posts: 1,131

Re: sql error in macro

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

Thanks,
Jag
Contributor
Posts: 66

Re: sql error in macro

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.

Super User
Posts: 17,864

Re: sql error in macro

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.

Contributor
Posts: 66

Re: sql error in macro

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

Super User
Posts: 6,946

Re: sql error in macro

Add

proc contents data=MBSF_AB&i.;run;


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 248 views
  • 1 like
  • 4 in conversation