topic Re: Combined select * statement along with select distinct statement? in SAS Enterprise Guide

Combined select * statement along with select distinct statement?

tinton — Wed, 17 May 2023 06:47:07 GMT

PROC SQL; CREATE TABLE T1 AS SELECT * FROM BOOK WHERE PORTFOLIO=''; // the output will show around 1,142,678 data
PROC SQL; CREATE TABLE T2 AS SELECT DISTINCT ID FROM T1; // the output will show only 68 id- 68 rows.

If I use above code, i will only get the distinct ID in T2. How can I get all the info in T1 by select distinct id? Meaning that i want T2 to show 68 id along with all column.

Re: Combined select * statement along with select distinct statement?

LinusH — Wed, 17 May 2023 06:59:02 GMT

You can't really do that (easily) in SQL.

PROC SORT with NODUPKEY.

Be aware if you have different values on different rows for the id column, which ones you want in the output data set.

If they are all the same, you hava duplicate rows, and then you could use DISTINCT in SQL.

Re: Combined select * statement along with select distinct statement?

tinton — Wed, 17 May 2023 07:05:37 GMT

Thanks for the reply! I've figure it out by using proc sort with nodupkey

DATA NO_DUP_TABLE; SET DUP_DATA; KEY = CATT(ID); RUN;

PROC SORT DATA= NO_DUP_TABLE NODUPKEY DUPOUT=TAB_DUP; BY KEY; RUN;