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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.