topic Re: Multiple proc sorts in SAS Procedures

Multiple proc sorts

Jackie_Stanbank — Thu, 17 Jun 2010 06:43:35 GMT

I would like to sort more than one dataset with a common by variable in one proc sort.

is this possible?
if so, please assist with syntax.

Re: Multiple proc sorts

LinusH — Thu, 17 Jun 2010 08:19:07 GMT

No.
If you want to minimize coding you cold wrap your sort into a macro.
/Linus

Re: Multiple proc sorts

sbb — Thu, 17 Jun 2010 12:28:42 GMT

Consider declaring a SAS macro variable (or a SAS79-style macro statement), such as:

%LET BY_VARS = A B C; - macro variable declared
BY &BY_VARS; - now reference the macro variable

..or..

MACRO _BYVARS A B C % - macro statement declared
BY _BYVARS; - now reference the macro statement

Scott Barry
SBBWorks, Inc.

Re: Multiple proc sorts

deleted_user — Thu, 17 Jun 2010 14:00:39 GMT

try this:

%macro st (ds);
proc sort data = &ds;
by var1 var2 var3;
run;
%mend;

%st(dataset1);
%st(dataset2);
.
.
.

GL

Re: Multiple proc sorts

Patrick — Fri, 18 Jun 2010 12:38:10 GMT

If you have to sort all these data sets in order to merge them using a common key then consider using PROC SQL instead of a data step as there data sets don't need to be pre-sorted for joining them.

Re: Multiple proc sorts

mmayorga — Tue, 22 Oct 2019 15:02:58 GMT

If the ultimate goal is to merge multiple datasets, then you can do it using PROC SQL.

The code below is an excerpt from "PROC SQL: Tips and Translations for Data Step Users" by Susan P Marcella & Gail Jorgensen, which you can find here: https://www.lexjansen.com/nesug/nesug10/hw/hw05.pdf

Instead of this:

proc sort data=L.drinkers; by id; run;
proc sort data=L.smokers; by id; run;
data L.LJSmokeDrinkdata;
merge L.smokers(in=smoke) L.drinkers(in=drink);
by id;
if smoke;
run;

Use this:

proc sql;
create table L.LJSmokeDrinkSQL as
select s.*, d.*
from L.smokers as s join L.drinkers as d
on s.id=d.id;
quit;

Alternatively, you can use PROC DATASETS and the CALL EXECUTE statement within a DATA step to sort datasets.

In the code below, I first create the dataset myMembers, which contains the variable NAME that gives me the names of all Datasets (memtype=data) on the WORK library.

Then, CALL EXECUTE allows me to run the same code on all datasets that have the letter 't' in their name (specified by the WHERE statement).

ods output Members=myMembers;
proc datasets lib=work memtype=data; run;
ods output close;

data _null_; set myMembers; 
	call execute ('proc sort data=work.'||strip(Name)||'; by Patient_ID; run;');
	where find(name,'t','i');
	run;

I usually reserve CALL EXECUTE for instances where I have several lines of code to do on at least 5 datasets, or simple code on more than 10 datasets.

Hope this helps! Good luck!

Re: Multiple proc sorts

mmayorga — Fri, 30 Aug 2019 15:05:42 GMT

I came up with another method.

If you want to sort all the datasets in a library, then it does make sense to use PROC DATASETS before using CALL EXECUTE.

Otherwise, you can list the datasets in a CARDS statement in the same DATA step as the CALL EXECUTE statement.

data _null_;
   input datasets $char50. ;
   call execute 
      ("proc sort data=work."||strip(datasets)||
       "(keep=cum_freq cum_date rename=(cum_freq="||strip(datasets)||"))"||
       "out="||strip(datasets)||";"||
       "by ByVariable; run;");
   cards;
      dataset1
      dataset2
      dataset3
   ;run;