Simple macro to run a proc sql - error

[saspert]

Hi,

I have a rather simple macro that I am using to run a proc sql. I am not able to resolve the error. Any help would be appreciated.

%macro _countrec_updatetaxid(taxid);
proc sql;
create table work.extract as
(
select count(*) :into rec_cnt from &FLDR..&FLDR._mcid_etg
where etg1tax contains
('&taxid')

);
QUIT;

%mend _countrec_updatetaxid;
*%local taxid rc;
%let taxid=43612589;
%let rc=%_countrec_updatetaxid(&taxid);
%put "The number of records for tax id is" &rc;

The error is

NOTE: Line generated by the invoked macro "_COUNTREC_UPDATETAXID".

29         proc sql; create table work.extract as  ( select count(*) :into rec_cnt from &FLDR..&FLDR._mcid_etg where etg1tax

                    ______

                     180

29       ! contains  ('&taxid')  );  QUIT;

ERROR 180-322: Statement is not valid or it is used out of proper order.

I think the bug is small but I am not able to notice what is wrong with the code.

Thanks,

saspert.

PS: The bigger plan is to invoke the above macro for all the records of a dataset using call execute statement. That is why I am testing this macro first.

Message was edited by: saspert So here is the crux of my issue. I have a main sas dataset &FLDR..&FLDR._mcid_etg with several hundreds of taxids. Some of them (around 83 distinct taxids) have a missing leading 0 for them. Now, I have all 83 of them in a separate dataset called work.msng_taxid. So, I need to somehow do this repetitive task of fetching 1 taxid, counting the # of records in &FLDR..&FLDR._mcid_etg and updating it with the correct taxid. In the above example, 43612589 needs to be replaced with 043612589.

[ballardw]

('&taxid') maybe should be "&taxid" to resolve the macro variable reference and the parantheses shouldn't be needed.

[saspert]

thanks ballardw - i modified as per your suggestion. please see above if my updated code is correct.

[Linlin]

you need to change '&taxid' to "&taxid".

[art297]

Without getting into what you are really trying to do:

1) the :into rec_cnt really should be into :rec_cnt

2) you are trying to create a table (containing nothing) AND create a macro variable.  Don't you really want a proc sql noprint with no create table?

3) as ballardw mentioned, the macro variable should be within double quotes.

[saspert]

Thanks Art,

So I modified my code but still am getting the same error. Now, if remove the %macro wrapper, it seems to be running fine.

NOTE: Line generated by the invoked macro "_COUNTREC_UPDATETAXID".

30         proc sql noprint;   select count(*) into :rec_cnt  from &FLDR..&FLDR._mcid_etg where etg1tax contains  '&taxid'   ;  QUIT

                               ______

                               180

30       ! ;

ERROR 180-322: Statement is not valid or it is used out of proper order.

Thanks,

saspert

[Tom]

The main problem is this statement:

%let rc=%_countrec_updatetaxid(&taxid);

SAS macros are not functions that return values.  They are mainly just a way to generate code to reduce repetitive typing.

In this case the existence the %LET statement preceding the macro call will cause the generated code to look like:

%let rc=proc sql ;

create ....

So the macro variable RC will be set to "PROC SQL" and the CREATE statement will generate an error message because it is not inside of a PROC SQL/QUIT block.

[saspert]

Thanks Tom for pointing out the glaring mistake. So, I guess I have to declare a global macro variable inside the macro program to store the output of the proc sql count and then use it outside the macro code?

[Tom]

The macro variable just needs to be defined outside of the scope of the macro for the value to continue to be visible after the macro ends.

%macro _countrec_updatetaxid(taxid);
proc sql noprint;

  select count(*) into :rec_cnt

  from &FLDR..&FLDR._mcid_etg
  where etg1tax contains "&taxid"

;
quit;

%mend _countrec_updatetaxid;

%let rec_cnt=0;

%_countrec_updatetaxid(XYZ);

%put Record count for XYZ = &rec_cnt ;

[saspert]

Thanks Tom,

I would like to call this macro for each observation in a dataset. Would this work?

data _null_;

set HrtgMsngTaxids ;

put taxid=;

call symput('_countrec_updatetaxid',taxid);

put &cnt_rec;

run;

[lacrefa]

If I remember well you can do that by 2 main way :

1 filename xxx temp lrecl=256;

data _null_;

set HrtgMsngTaxids ;

length text $200;

file xxx;

text=' %let rec_cnt=0;';output;

text=trim(text)||' %_countrec_updatetaxid('||taxid||');';

text=trim(text)||'%put Record count for XYZ = &rec_cnt ;';

put text;

run;

%inc xxx;

filename xxx;

2 instead to put statement in temp file, use a  : call execute(text)

[Tom]

If that is what you want to do then you do not want to use a macro or even macro variables.

Just join the table and count the records.

proc sql noprint ;

create table want as

   select distinct

         a.taxid

       , count(etg1tax) as cnt_rec

   from hrtgmsngtaxids a left join

        &FLDR..&FLDR._mcid_etg b

   on  b.etg1tax contains trim(a.taxid)

    group by a.taxid

;

quit;

[Astounding]

If you are headed toward CALL EXECUTE, there is an issue to keep in mind.  The PROC SQL statements generated by CALL EXECUTE will stack up and execute once the DATA step that generates them has completed.  But any macro language statements generated by CALL EXECUTE (%LET, %PUT, for example) will execute right away, before the current DATA step has completed, and before any of the PROC SQL steps have run.

[saspert]

Thanks astounding - i noticed that when i ran the datastep with only 2 records. It seem to complete the datastep first and then execute the macro. Anyway, I went ahead and manually updated the dataset since I am short of time.

saspert.

[qian]

Hi, You need double quatation, instead single one, for the macro variable &taxid to work.
So try

where etg1tax contains

("&taxid")

Good luck!