BookmarkSubscribeRSS Feed
gaultie
Calcite | Level 5

SAS discussion board

Anyone have any idea how PROC SQL orders returned data for variables which have not been explicitly ordered or if there is a way of controlling it - for example with a many to many merge. Is there a rule about the order in which variables which are not explicitly part of the merge will be ? It does not seem to necessarily be the order in which they were in the source data. We are using SAS 9.3
 
With proc sort there is a system option (SORTEQUALS) which forces the order of the non sorted variables to be as in the source data but is there a similar control with proc sql ?
 
Good practice will be to explicitly include the desired order in the proc sql but just wondering if there is a solution that does not require a change to code (other than perhaps an option). The problem has arisen as part of a move to SAS Enterprise Guide. Oddly exactly the same proc sql code returns observations in a different order (within by group) if submitted from a display manager interface compared to if submitted from Enterprise Guide. I can't reproduce with a simple dummy example.
 
2 REPLIES 2
Tom
Super User Tom
Super User

SQL makes no guarantee on ordering. You might be seeing the results of running on multiple threads causing a change in the order.

 

If you really do not have a set of keys then just order on everything.  You can use PROC SORT and the _ALL_ variable list. If you did have some partial keys that you want to order on add those in front of the _ALL_ keyword.

proc sort data=have;
  by key1 key2 _all_;
run;
ChrisBrooks
Ammonite | Level 13

If I can echo what Tom says SQL makes no guarantee of order. In fact the SQL language was designed for relational databases and when I was taught relational database theory (many decades ago) I was told that in an RDBMS order of records should never matter.

 

If you're running your code against say ORACLE it's possible, as Tom says, that internally ORACLE is threading your query without you realising it.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 659 views
  • 0 likes
  • 3 in conversation