DATA Step, Macro, Functions and more

Simple macro to run a proc sql - error

Reply
Super Contributor
Posts: 269

Simple macro to run a proc sql - error

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.

Super User
Posts: 10,552

Re: Simple macro to run a proc sql - error

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

Super Contributor
Posts: 269

Re: Simple macro to run a proc sql - error

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

Super Contributor
Posts: 1,636

Re: Simple macro to run a proc sql - error

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

PROC Star
Posts: 7,366

Re: Simple macro to run a proc sql - error

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.

Super Contributor
Posts: 269

Re: Simple macro to run a proc sql - error

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

Super User
Super User
Posts: 6,502

Re: Simple macro to run a proc sql - error

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.

Super Contributor
Posts: 269

Re: Simple macro to run a proc sql - error

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?


Super User
Super User
Posts: 6,502

Re: Simple macro to run a proc sql - error

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 ;


Super Contributor
Posts: 269

Re: Simple macro to run a proc sql - error

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;


Contributor
Posts: 22

Re: Simple macro to run a proc sql - error

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)

Super User
Super User
Posts: 6,502

Re: Simple macro to run a proc sql - error

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;

Super User
Posts: 5,099

Re: Simple macro to run a proc sql - error

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.

Super Contributor
Posts: 269

Re: Simple macro to run a proc sql - error

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.

Occasional Contributor
Posts: 6

Re: Simple macro to run a proc sql - error

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

where etg1tax contains

("&taxid")

Good luck!

Ask a Question
Discussion stats
  • 16 replies
  • 1349 views
  • 6 likes
  • 9 in conversation