The SAS Output Delivery System and reporting techniques

Translation proc sort nodupkey to ANSI SQL

Posts: 0

Translation proc sort nodupkey to ANSI SQL

Dear all,

My colleague asks me to translate proc sort nodupkey to ANSI SQL (not proc SQL that can arrange some special PDVs).

Suppose that the source table has 10 variables and we would like to deduplicate 2 vars.

proc sort data=source out=dest nodupkey;
by var1 var2;

Would you please translate this statement to ANSI SQL?
Posts: 8,743

Re: Translation proc sort nodupkey to ANSI SQL

This is not really an ODS question. However, these papers may help in explaining more about PROC SORT and NODUPKEY: [pre] [/pre]

If the papers do not help you resolve your requirement, then Tech Support is probably your best bet. To find out how to contact Tech Support, go to [pre] [/pre]

Good luck!
Posts: 0

Re: Translation proc sort nodupkey to ANSI SQL

Thank you Cynthia,

I will follow up with technical support team.
But, is it possible to open discussion forum about Base or STAT ?

Other vendors have extensive of discussion websites but SAS doesn't have. Can you recommend me the external website ?

Best regards,

Message was edited by: CJr at Jan 16, 2007 3:05 AM

Message was edited by: CJr at Jan 16, 2007 3:06 AM
Message was edited by: CJr at Jan 16, 2007 3:07 AM
Posts: 8,743

Re: Translation proc sort nodupkey to ANSI SQL

In my mind, SQL along with PROC SORT, falls into the category of "SAS programming" more than reporting. Even though I know some folks use SQL for reporting -- I'm more a practitioner of PROC REPORT, PROC TABULATE and DATA step for reporting.

For general SAS programming questions, the SAS on-line documentation is a great place to start. The SAS sample programs are really very good. If you are using SAS Display Manager for your programming, there is a Quick Start Guide that you can access (on Windows, at least), that I have been told is quite handy and informative. In addition, there are Self-paced e-learning modules on programming available through SAS publications.

And of course, there are all our training classes. I think the issue of NODUPKEY and PROC SORT is covered in chapter 6 of our Advanced Programming class. (But we do not show an SQL alternative to PROC SORT in that class because of the "apples" and "oranges" aspect of the comparison. Proc SORT does one thing --sort data; while PROC SQL muddies the waters with the ability to select columns, create columns, order columns, group columns, etc all in one fell swoop.) The use of the DISTINCT keyword with PROC SQL to eliminate duplicates is covered in the SQL class that we offer.

But, I digress, you wanted to know about setting up a basic discussion forum. If you go here, you can enter a suggestion on how to improve the SAS Customer Support Web site: .

Then, if you go to
you will find a LOT of information for the Statistics and Operations Research community. And this site shows all the SAS communities which have some form of FAQ or example section.

There is also a Listserv that is not hosted by SAS called SAS-L. Information about joining the SAS-L listserv can be found here: and for more info on SAS-L, you can refer to this SUGI paper:

But I still think that Tech Support is your best resource for this specific question.
Ask a Question
Discussion stats
  • 3 replies
  • 2 in conversation