BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Linlin
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

9 REPLIES 9
Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
PROC Star

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

Haikuo
Onyx | Level 15

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

Thank you Tom!   Linlin

Haikuo
Onyx | Level 15

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

Thank All!

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1738 views
  • 7 likes
  • 5 in conversation