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;
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 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.
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.
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.;
Add
proc contents data=MBSF_AB&i.;run;
to your macro loop and look at the output(s) of that.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.