topic Re: Simple macro to run a proc sql - error in SAS Programming

Simple macro to run a proc sql - error

saspert — Wed, 10 Oct 2012 22:16:22 GMT

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.

Re: Simple macro to run a proc sql - error

ballardw — Wed, 10 Oct 2012 22:44:25 GMT

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

Re: Simple macro to run a proc sql - error

art297 — Wed, 10 Oct 2012 22:45:34 GMT

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.

Re: Simple macro to run a proc sql - error

saspert — Thu, 11 Oct 2012 00:29:45 GMT

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

Re: Simple macro to run a proc sql - error

saspert — Thu, 11 Oct 2012 00:30:35 GMT

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

Re: Simple macro to run a proc sql - error

Linlin — Thu, 11 Oct 2012 01:10:48 GMT

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

Re: Simple macro to run a proc sql - error

Tom — Thu, 11 Oct 2012 02:05:52 GMT

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.

Re: Simple macro to run a proc sql - error

saspert — Thu, 11 Oct 2012 02:27:06 GMT

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?

Re: Simple macro to run a proc sql - error

Tom — Thu, 11 Oct 2012 11:11:20 GMT

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 ;

Re: Simple macro to run a proc sql - error

saspert — Thu, 11 Oct 2012 12:14:48 GMT

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;

Re: Simple macro to run a proc sql - error

lacrefa — Thu, 11 Oct 2012 13:41:13 GMT

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)

Re: Simple macro to run a proc sql - error

Astounding — Thu, 11 Oct 2012 14:06:50 GMT

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.

Re: Simple macro to run a proc sql - error

saspert — Thu, 11 Oct 2012 14:24:51 GMT

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.

Re: Simple macro to run a proc sql - error

qian — Thu, 11 Oct 2012 16:04:36 GMT

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

where etg1tax contains

("&taxid")

Good luck!

Re: Simple macro to run a proc sql - error

JeremyCAdams — Thu, 11 Oct 2012 17:49:47 GMT

You could make the macro return a value if you really have to have it that way, but it's not great programming practice in SAS:

%MACRO checkForVars(var1,var2,var3);
    %let var1=%sysfunc(dequote(&var1));
    %let var2=%sysfunc(dequote(&var2));
    %let var3=%sysfunc(dequote(&var3));
    %let dsid=%sysfunc(open(&inDS));
    %let check=%sysfunc(varnum(&dsid,&var1));
    %if &check ^= 0 %then %do;
        %let check=%sysfunc(varnum(&dsid,&var2));
        %if &check ^= 0 %then %do;
            %let check=%sysfunc(varnum(&dsid,&var3));
            %let rc=%sysfunc(close(&dsid));
            %if &check ^= 0 %then %let out=Y;
            %end;
        %end;
        %else %do;
        %let rc=%sysfunc(close(&dsid));
        %let out=N;
        %end;
&out
%MEND checkForVars;
%let end_date_flg="%checkForVars('cdexpm','cdexpd','cdexpy')";

Notice the missing semicolon at the &out part of the macro. This will send the value of the variable out back to wherever the macro was called from. In this case, this macro sends back a value of "Y" or "N" to indicate whether one of the three variables passed to it exists in the dataset.

Re: Simple macro to run a proc sql - error

Tom — Thu, 11 Oct 2012 18:57:03 GMT

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;

Re: Simple macro to run a proc sql - error

lacrefa — Fri, 12 Oct 2012 08:18:06 GMT

Dear

You can have a view using the "Hash Iterator Object" inside this document "http://support.sas.com/rnd/base/datastep/dot/iterator-getting-started.pdf"

This one use a comparison between date and you can adapte using index funtion. The small table can be used as hash table.

Regards