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
... View more
