topic Re: Do not want DB to sort the data in SAS Programming

Do not want DB to sort the data

RahulG — Mon, 14 Nov 2016 05:53:48 GMT

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.

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.

Re: Do not want DB to sort the data

Kurt_Bremser — Mon, 14 Nov 2016 07:48:16 GMT

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;

Re: Do not want DB to sort the data

RahulG — Mon, 14 Nov 2016 09:18:52 GMT

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.

Re: Do not want DB to sort the data

Shmuel — Mon, 14 Nov 2016 10:06:13 GMT

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 @Kurt_Bremser, 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 */

Re: Do not want DB to sort the data

LinusH — Mon, 14 Nov 2016 12:47:18 GMT

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