BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RahulG
Barite | Level 11

 

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

Data never sleeps

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User
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;
RahulG
Barite | Level 11

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.

Shmuel
Garnet | Level 18

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

 

LinusH
Tourmaline | Level 20

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

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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