DATA Step, Macro, Functions and more

Do not want DB to sort the data

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

Do not want DB to sort the data

 

I have DB table and want to use in SAS server. I need sorted data then I can use by statement in data step to skip sorting.

Ex 

data sample;

set DB.sample;

by Id ;

run;

 

For more detail, You can refer to this post 

With reference to this post 

http://blogs.sas.com/content/sasdummy/2016/02/04/avoid-sorting-data-in-sas/.

 

My requirement is to sort on SAS server and I do not want DB server to sort the data. Reason is DB server and SAS server are sorting based on different language. What option should I provide so that sorting in DB can be stopped. 


Accepted Solutions
Solution
‎11-14-2016 10:52 PM
Super User
Posts: 5,426

Re: Do not want DB to sort the data

Perhaps the SORTPGM=SAS system option might be what you are looking for?

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 7,766

Re: Do not want DB to sort the data

proc sort
  data=db.sample
  out=sample
;
by id;
run;

If that does not prevent the wrong sort (as SAS might pass off some of the work to the DB), do

data sample;
set db.sample;
run;

proc sort data=sample;
by id;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: Do not want DB to sort the data

Posted in reply to KurtBremser

Yes I can do that. But I have many such data step so I would end up writing too many code. 

I will go by this solution, if there are none.

Trusted Advisor
Posts: 1,554

Re: Do not want DB to sort the data

If you want that the work will be done in the SAS server, then you need bring your data to it.

As you need many DB tables, in order to downsize the SAS code, use a macro to do the work

and supply it an argumnt of table names.

 

I shall use first code presented by @KurtBremser, but you can change the inner code to any desired one.

 

%macro get_db(tables);

      %let no_of_tables = %sysfunc(countw(&tables));

      %do i=1 %to &no_of_tables;
              %let tb_name = %scan(&tables, &i);

              

proc sort data=db.&tb_name  out=&tb_name ;
  by id;
run;

      %end;

%mend get_db;

%get_db(sample1 sample2 sample3 ... );  /* enter DB tables you need */

 

Solution
‎11-14-2016 10:52 PM
Super User
Posts: 5,426

Re: Do not want DB to sort the data

Perhaps the SORTPGM=SAS system option might be what you are looking for?

Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 267 views
  • 0 likes
  • 4 in conversation