How to CALL EXECUTE

[pavank]

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

[Kurt_Bremser]

/* 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)

[andreas_lds]

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

[pavank]

sort all datasets by common variable

[andreas_lds]

---

@pavank wrote:

sort all datasets by common variable

---

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

[Tom]

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.

[pavank]

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

[Kurt_Bremser]

Have you already run my code?

[pavank]

yes i did 

[Kurt_Bremser]

So you have your solution.

[Reeza]

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-Ap...

---

@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

---