DATA Step, Macro, Functions and more

Getting data from DB2 - PROC SORT/SQL do not sort correctly

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Getting data from DB2 - PROC SORT/SQL do not sort correctly

I am getting a table from a DB2 database, and needing to filter and sort it before passing it to the next data step. With both PROC SORT and PROC SQL, the table does not sort. I get an error telling me so.

 

I have "fixed" the problem by putting a 2nd PROC SORT with the PRESORTED option which a)verifies it was not sorted and b)does the sorting. I'd just like to understand the issue.

 

The original codes, neither of which sorted. I've already made a libref to the db2 schema ('jpod'),

proc sql;
	create table iqik as select *
		from jpod.idx_queue_item_keys
			where substr(queue_nm,1,2) in ('cl')
				order txn_id,queue_nm,arrival_ts
	;
quit;

OR

proc sort data=jpod.idx_queue_item_keys out=iqik;
/*	where substr(queue_nm,1,2) in ('cl');*/
	by txn_id queue_nm ARRIVAL_TS;
run;

Accepted Solutions
Solution
‎02-08-2017 04:31 PM
Super Contributor
Posts: 474

Re: Getting data from DB2 - PROC SORT/SQL do not sort correctly

Hi.

 

Yes, you've got it right.

 

We had the same problem here with DB2, which was solved by recreating the DB2 sequence in SAS throught a TRANTAB.

 

Also, using non pass-through SQL won't guarantee that the data is sorted on the SAS side.

 

The SAS/Access engine might push it to the database.

 

You can check what is running and where by activating the SASTRACE option like this:

 

options sastrace=',,,d' sastraceloc=saslog;

 

If you see an "order by" being pass, then it might be the problem... If not, then it's something else.

 

Which DB system are you using? DB2/LUW or not?

 

Daniel Santos @ www.cgd.pt

View solution in original post


All Replies
Super Contributor
Posts: 474

Re: Getting data from DB2 - PROC SORT/SQL do not sort correctly

[ Edited ]

Hi.

 

You might be getting the sorting sequence of the database, which may not be the same sort sequence of SAS.

 

If that's the case you should sort the data only within SAS (using PROC SORT).

 

If you really need to order the data in the database side, you should create a translation table (with PROC TRANTAB) with the correspoding sort sequence and make it default for your SAS session.

 

More info on PROC TRANTAB here: http://support.sas.com/documentation/cdl/en/nlsref/69741/HTML/default/viewer.htm#p15gnr2l8y46o5n116v...

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

Contributor
Posts: 69

Re: Getting data from DB2 - PROC SORT/SQL do not sort correctly

Let me make sure I understand what you mean by sorting sequence... in the same way that if you sort a column that contains capitalized and non-capitalized words in SAS and Excel, you get different results (alphabetical in Excel vs ASCII or whatever the SAS method is); DB2 and SAS may sort the same values differently. Is that what you meant? And then that the ORDER BY is using that DB2 method rather than the SAS method.

 

That would surprise me (though I'm not saying you're wrong). First, because I wrote regular proc sql and not pass-through in the DB2 syntax (there it would make more sense to use the DB2 sorting). We do use DB2 pass-through at times-- i did not here because there were little time savings to gain (our main reason for doing so in other cases). Second, because my assumption is that the order by is the last operation done by the query and I would have assumed the table is basically already built in SAS at that point (but admittedly I'm quite ignorant of how this actually works).

 

That being said.. it certainly fits what I'm experiencing.

Solution
‎02-08-2017 04:31 PM
Super Contributor
Posts: 474

Re: Getting data from DB2 - PROC SORT/SQL do not sort correctly

Hi.

 

Yes, you've got it right.

 

We had the same problem here with DB2, which was solved by recreating the DB2 sequence in SAS throught a TRANTAB.

 

Also, using non pass-through SQL won't guarantee that the data is sorted on the SAS side.

 

The SAS/Access engine might push it to the database.

 

You can check what is running and where by activating the SASTRACE option like this:

 

options sastrace=',,,d' sastraceloc=saslog;

 

If you see an "order by" being pass, then it might be the problem... If not, then it's something else.

 

Which DB system are you using? DB2/LUW or not?

 

Daniel Santos @ www.cgd.pt

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 145 views
  • 0 likes
  • 2 in conversation