topic How to use call execute in a datastep or macro? in SAS Programming

How to use call execute in a datastep or macro?

Linlin — Wed, 21 Mar 2012 16:41:51 GMT

Hi All;

how can I use call execute to do something like below?

Thank you very much! - Linlin

data have;

input id;

cards;

;

/* */

if id=3 then do;

proc sql;

drop table temp;

quit;

How to use call execute in a datastep or macro?

Haikuo — Wed, 21 Mar 2012 16:52:28 GMT

Not sure about your purpose, but this is my try:

data temp;

a=1;

run;

data have;

input id;

if id=3 then call execute('proc sql;

drop table temp;

quit;');

cards;

;

Regards,

Haikuo

How to use call execute in a datastep or macro?

OS2Rules — Wed, 21 Mar 2012 17:35:13 GMT

I usually use this...

data have;

input id;

cards;

;

/* */

%macro mysql1;

proc sql;

drop table temp;

quit;

run;

%mend;

data _null_;

set have;

if id=3 then do;

call execute('%mysql1');

end;

run;

How to use call execute in a datastep or macro?

Linlin — Wed, 21 Mar 2012 18:02:42 GMT

Hi Haikuo,

Thank you!

I tried to convert your code to a macro. Can you help me to figure out why the first code works, the second has error message in the Log file?

/* first */

data temp;

a=1;

run;

%let dsn=temp;

%let lib=work;

data have;

input id;

if id=3 then call execute('proc sql;

drop table &lib..&dsn;

quit;');

cards;

;

run;

/* Second */

data have;

infile cards missover;

input Name $ DOB :$10. (Add1 Add2 Addr3) (:$);

cards;

John 10/10/80 data data

Alan 11/11/81 data

Paul 10/10/79 data

;

run;

%macro test(lib,dsn);

proc sql noprint;

select catx(' ','nmiss(',name,') as',name) into : list separated by ','

from dictionary.columns

where libname=upcase(&lib) and memname=upcase(&dsn);

select count(*) into : nobs from have;

create table temp as

select &list from have;

quit;

data _null_;

set temp;

array _x{*} _numeric_;

do i=1 to dim(_x);

if _x{i} eq &nobs then do;

call execute('proc sql;

drop table &lib..&dsn;

quit;');

stop;end;

end;

run;

%mend;

%test(work,have)

From log file:

1686 ;

1687 run;

1688 %macro test(lib,dsn);

1689 proc sql noprint;

1690 select catx(' ','nmiss(',name,') as',name) into : list separated by ','

1691 from dictionary.columns

1692 where libname=upcase(&lib) and memname=upcase(&dsn);

1693 select count(*) into : nobs from have;

1694 create table temp as

1695 select &list from have;

1696 quit;

1697 data _null_;

1698 set temp;

1699 array _x{*} _numeric_;

1700 do i=1 to dim(_x);

1701 if _x{i} eq &nobs then do;

1702 call execute('proc sql;

1703 drop table &lib..&dsn;

1704 quit;');

1705

1706 stop;end;

1707 end;

1708 run;

1709 %mend;

1710 %test(work,have)

ERROR: The following columns were not found in the contributing tables: have, work.

NOTE: Table WORK.TEMP created, with 1 rows and 5 columns.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 1 observations read from the data set WORK.TEMP.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: CALL EXECUTE generated line.

1 + proc sql;

1 + drop table work.have;

NOTE: Table WORK.HAVE has been dropped.

1 + quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

How to use call execute in a datastep or macro?

Astounding — Wed, 21 Mar 2012 18:11:03 GMT

Your WHERE clause needs double quotes around &LIB and &DSN

How to use call execute in a datastep or macro?

Haikuo — Wed, 21 Mar 2012 18:13:39 GMT

where libname=upcase(&lib) and memname=upcase(&dsn);

Should you use upcase("&lib")? otherwise it evalutes on expression(which does not exist in this case), instead of values?

And I can't weigh in on whether you should just use %upcase.

Regards,

Haikuo

How to use call execute in a datastep or macro?

Linlin — Wed, 21 Mar 2012 18:27:12 GMT

Thank All!

Yes, I need double quotes around &LIB and &DSN.

How to use call execute in a datastep or macro?

Tom — Wed, 21 Mar 2012 18:35:25 GMT

You should use "%upcase(&lib)" because this will be evaluated only once by the macro processor.

If you use upcase("&lib") then the UPCASE() function will need to evaluate for every record pulled from DICTIONARY.COLUMNS.

Although if the SQL parser is smart enough it could tell that this is a constant and factor it out.

How to use call execute in a datastep or macro?

Linlin — Wed, 21 Mar 2012 18:49:38 GMT

Thank you Tom! Linlin

How to use call execute in a datastep or macro?

Haikuo — Wed, 21 Mar 2012 18:59:37 GMT

Learned again! Thank you very much, Tom! It was actaully fairly simple after your explanation. Now I know when I should use these %macro functions!