topic Re: How to CALL EXECUTE in SAS Programming

How to CALL EXECUTE

pavank — Wed, 28 Jul 2021 07:44:17 GMT

data ds;
set sashelp.class;
run;

data ds1;
set sashelp.class;
run;

data ds2;
set sashelp.class;
run;



data _null_;
set sashelp.vtable;
where libname = upcase("&lib.");
call execute( "proc sort data=&lib.." !! strip(memname) !! ";
              by &sortvar.;
              run; ");
run;

suppose common variable age sort how to call the macro variable

Re: How to CALL EXECUTE

Kurt_Bremser — Wed, 28 Jul 2021 07:43:55 GMT

/* clean up first */
proc datasets lib=work kill;
quit;

data ds;
set sashelp.class;
run;

data ds1;
set sashelp.class;
run;

data ds2;
set sashelp.class;
run;

%let lib=WORK;
%let sortvar=age;

data _null_;
set sashelp.vtable;
where libname = upcase("&lib.");
call execute( "proc sort data=&lib.." !! strip(memname) !! ";
              by &sortvar.;
              run; ");
run;

If you want to make this a macro, add the proper definition statements around your code, and call the macro:

%macro sort_all(lib=,sortvar=);

data _null_;
set sashelp.vtable;
where libname = upcase("&lib.");
call execute( "proc sort data=&lib.." !! strip(memname) !! ";
              by &sortvar.;
              run; ");
run;

%mend;

%sort_all(lib=work,sortvar=age)

Re: How to CALL EXECUTE

andreas_lds — Wed, 28 Jul 2021 09:51:53 GMT

Not clear (at least for me) what you are trying to achieve in the long run. Can you explain what you expect as result?

Re: How to CALL EXECUTE

pavank — Wed, 28 Jul 2021 11:48:11 GMT

sort all datasets by common variable

Re: How to CALL EXECUTE

andreas_lds — Wed, 28 Jul 2021 12:01:48 GMT

@pavank wrote:

sort all datasets by common variable

Thanks. Looking at the code suggest by @Kurt_Bremser, i don't see potential for optimization.

Re: How to CALL EXECUTE

Tom — Wed, 28 Jul 2021 12:48:11 GMT

I don't understand what your question is.

suppose common variable age sort how to call the macro variable

The code you posted was using the macro variable SORTVAR.

To assign a value to that macro variable you can use %LET statement.

%let sortvar=age;

If that was not your question then please try re-phrasing the question to make it clearer.

Re: How to CALL EXECUTE

Reeza — Wed, 28 Jul 2021 14:46:08 GMT

A really good idea when using CALL EXECUTE is to create the code in a string first and then pass that to CALL EXECUTE. This way you can check if the string is being generated correctly before using CALL EXECUTE. I also recommend using CATT() and putting each portion on a separate line.

As @Tom already indicated, you also need to declare your macro variable ahead of time.

data ds;
set sashelp.class;
run;

data ds1;
set sashelp.class;
run;

data ds2;
set sashelp.class;
run;

%let sortVar = Age;

data _null_;
set sashelp.vtable;
where libname = upcase("&lib.");
*I put each portion of the string on a separate line to make it easy to ensure quotes, commas and parenthesis are correctly specified;
str = CATT( "proc sort data=&lib.." ,
                      strip(memname),
                      "; by &sortvar.; run; "
                 );
call execute(str);
run;

Some references that may be helpful:

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro <- typically if you follow these steps in your development your code will almost always be correct and working.

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@pavank wrote:

data ds;
set sashelp.class;
run;

data ds1;
set sashelp.class;
run;

data ds2;
set sashelp.class;
run;



data _null_;
set sashelp.vtable;
where libname = upcase("&lib.");
call execute( "proc sort data=&lib.." !! strip(memname) !! ";
              by &sortvar.;
              run; ");
run;

suppose common variable age sort how to call the macro variable

Re: How to CALL EXECUTE

pavank — Thu, 29 Jul 2021 09:36:20 GMT

For sort all datasets pre request it must be common variable and we can not sort all datasets without common variable in a library

Re: How to CALL EXECUTE

Kurt_Bremser — Thu, 29 Jul 2021 09:51:01 GMT

Have you already run my code?

Re: How to CALL EXECUTE

pavank — Thu, 29 Jul 2021 09:59:23 GMT

yes i did

Re: How to CALL EXECUTE

Kurt_Bremser — Thu, 29 Jul 2021 10:41:50 GMT

So you have your solution.