BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

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.

16 REPLIES 16
ballardw
Super User

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

saspert
Pyrite | Level 9

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

Linlin
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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
Pyrite | Level 9

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
Super User Tom
Super User

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
Pyrite | Level 9

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
Super User Tom
Super User

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
Pyrite | Level 9

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
Calcite | Level 5

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
Super User Tom
Super User

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
PROC Star

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
Pyrite | Level 9

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
Calcite | Level 5

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

where etg1tax contains

("&taxid")

Good luck!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 2543 views
  • 6 likes
  • 9 in conversation