Hi All;
how can I use call execute to do something like below?
Thank you very much! - Linlin
data have;
input id;
cards;
1
2
3
;
/* */
if id=3 then do;
proc sql;
drop table temp;
quit;
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;
1
2
3
;
Regards,
Haikuo
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;
1
2
3
;
Regards,
Haikuo
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;
1
2
3
;
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
Your WHERE clause needs double quotes around &LIB and &DSN
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
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.
Thank you Tom! Linlin
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!
I usually use this...
data have;
input id;
cards;
1
2
3
;
/* */
%macro mysql1;
proc sql;
drop table temp;
quit;
run;
%mend;
data _null_;
set have;
if id=3 then do;
call execute('%mysql1');
end;
run;
Thank All!
Yes, I need double quotes around &LIB and &DSN.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.