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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11

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

3 REPLIES 3
DanielSantos
Barite | Level 11

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

cau83
Pyrite | Level 9

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.

DanielSantos
Barite | Level 11

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

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