CODEWITHSUNDEEP
sas
Raven
Raven

Reputation: 859

Proc Sort Using a Macro

I created 40 plus tables (using a marco, which I just learned how to do) that I would like to apply the Proc Sort statement to. I want each table sorted by the same variable 'Account_Description' (each table contains this variable).

The table names are June_53410_v1, June_53420_v1, June_53430_v1, etc. Can I employ a macro, and if so, how can I, to mitigate having to write a proc sort statement for each table?

Thanks!

I found this sample code online but I'm not really sure how it works

 %Macro sorter(dsn, var);
 proc sort data=&dsn.;
 by &var.;
 run;
 %mend;
 %sorter(sample_dataset, age);

Upvotes: 2

Views: 1857

Answers (3)

Llex
Llex

Reputation: 1770

You can use this solution, where lib is libname, mask_table is mask to table(June_ in your task) and var is variable to sort tables: 

 %macro sorter(lib,mask_table, var); %macro d;%mend d;

       %let table_list = 0;

       proc sql noprint;
          select strip(libname) || '.' || strip(memname),count(memname)
          into: table_list separated by ' '
          from dictionary.tables 
          where libname = UPCASE("&lib.") and memname LIKE UPCASE("&mask_table.")||"%";
       quit;

       %do i=1 %to %sysfunc(countw(&table_list,%str( )));
          %let name&i = %scan(&table_list, &i, %str( ));
          proc sort data=&&name&i.;
             by &var.;
          run;
       %end;

  %mend sorter;
  %sorter(WORK,June,Account_Description);

Upvotes: 1

Raven
Raven

Reputation: 859

I appreciate everyone's input-I think I found an answer though using this code: 

 %macro st (ds);
 proc sort data = &ds;
 by Account_Description;
 run;
 %mend;

 %st(June_53410_v1); 
 %st(June_53420_v1);

Upvotes: 1

Sanek Zhitnik
Sanek Zhitnik

Reputation: 726

Macro that will be used (proc sort write to work):

%Macro sorter(lib,dsn, var);
proc sort data=&lib..&dsn. out=&dsn.;
by &var.;
run;
%mend;

Get dictionary of tables that contains in name some chars (its maby “June_” instead “AIR”) :

data sashelp_tables;
set sashelp.vtable;
where LIBNAME="SASHELP" and MEMNAME contains "AIR"
;
run;

Write code to string , and execute it for all tables:

data _NULL_;
length code $ 200;
set sashelp_tables;
code=cat('%sorter(',LIBNAME,',',MEMNAME,',AIR);');
call execute(code);
run;

Upvotes: 1

Related Questions