How to use call execute in a datastep or macro?

[Linlin]

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;

[Haikuo · Accepted solution]

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

[Linlin]

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

[Astounding]

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

[Haikuo]

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

[Tom]

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.

[Linlin]

Thank you Tom!   Linlin

[Haikuo]

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!

[OS2Rules]

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; 

[Linlin]

Thank All!

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