character length in proc sql versus data step

[ChrisNZ]

It should.
Still a bug though, SAS should not ask for a sort that cannot take place. And still no reason for whichever application does this to truncate values.

Another way to know if the passthrough is the culprit is to use a where clause in the data step, if I am not mistaken.

[mattmoehr · Accepted solution]

Editor's note: This question/challenge is specific to using PROC SQL with Microsoft Access and the SAS/ACCESS to PC Files engine.

@PGStats I'm not going to start using passthrough. Then my macros would be dependent on MS Access-specific sql, which is worse than the current situation. If I have to do a re-write, I'm going to move this into data steps.

@ChrisNZ The data steps work just fine with either the if statement or the where statement to select rows.

I've taken out the sort from SQL statement, but that does not seem to have any effect.

I think it all comes down to how the distinct clause is implemented. Apparently, it is only wants to "distinct-ify" the first 255 characters in a DB field, so it only spits out the first 255 chars? I dunno. I've removed the distinct clause from my production macro and the first use case, seems to work with our desired behavior.

[PGStats]

I did not suggest to use SQL pass through. I suggested to use option NOIPASSTHRU which prevents SAS/SQL to issue implicit pass through commands to MS-Access.

[mattmoehr]

Sorry, I misread your suggestion about implicit pass through. I will try to test that approach as it seems the safest/least likely to break when other software changes. The SAS tech suggested something about editting my registry for 'typeguessrows', but I don't think I want to put time into that approach because it requires admin privliges on each users' machine, which I may not have.

Is this the best documentation for NOIPASSTHRU?

... I was hoping there was a list of DB-specific functions that were implicitely passed. Does such a list exist anywhere?

[mattmoehr]

http://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#p12ohgh32ffm6un13s...

[mattmoehr]

(That's the link to the SAS docs. I put it in a "quick reply" because the comment editor does not recognize the link to SAS's documentation as a valid URI. Probably because it has two # bookmarks in it.)

[Kurt_Bremser]

---

@mattmoehr wrote:

@PGStats I'm not going to start using passthrough. Then my macros would be dependent on MS Access-specific sql, which is worse than the current situation. If I have to do a re-write, I'm going to move this into data steps.

---

But this is what actually happens. SAS hands the "distinct" over to Access so that the sorting is done within the database (default SAS behaviour when using ACCESS to Relational Databases). DATA steps don't do that, and forcing SAS to do the necessary sort by itself when using SQL should also get a clearer view on the problem.