proc sql instead of proc sort nodupkey

[michtka]

Hi everyone, I got the new dataset and code below:  Can anyone help me to use proc sql to obtain the same tables news and news2  which were obtained using (proc sort no dupkey)? Thanks.

*n=16 records;

    data new;
    length usubjid visit trt race aesoc $10 baseline 8 age 8 date 8;
informat date mmddyy10.;
format date date9.;

    input usubjid visit baseline aesoc race date age trt;
datalines;
100001  visit1  0.5  head   black  05/10/1998  45  drug
100001  visit2  1.2  leg    black  05/03/2002  45  drug
    100001  visit2  1.2  head   black  05/03/2002  45  drug
100001  visit3  1.4  head   black  05/04/2006  45  drug
100001  visit4  1.5  hand   black  05/04/2008  45  drug
100001  visit5  1.6  leg    black  05/08/1996  45  placebo
100002  visit1  0.8  hand   latin  05/09/1994  34  placebo
100002  visit2  1.4  head   latin  04/09/1994  34  placebo
100002  visit3  1.5  blood  latin  02/08/1999  34  placebo
100002  visit4  1.3  blood  latin  12/12/2008  34  placebo
100003  visit1  0.6  head   asian  12/14/1979  36  drug
100003  visit2  1.5  hand   asian  12/12/1973  36  drug
100003  visit3  1.2  shoulder asian 12/08/1976 36  drug
100003  visit2  1.5  hand   asian   12/12/1973 36  drug
100004  visit1  1.6  hand   caucasian 12/12/1987 38 placebo
    100005  visit1  0.4  head   white      12/12/1976 39 drug
    ;
    run;

     *n=14 records;

    proc sort data=new out=news nodupkey;
by usubjid visit;
    run;
    

    *n=15 records;

proc sort data=new out=news2 nodupkey;
by usubjid visit aesoc;
    run;

[michtka]

proc sql;

     create table news as
(select count(*) as count , usubjid,visit
     from new group by usubjid, visit);
     alter table news drop count;

     create table news2 as
(select count(*) as count , usubjid,visit, aesoc
     from new group by usubjid, visit, aesoc);
     alter table news2 drop count;
  quit;

[Haikuo · Accepted solution]

Your solution does not reproduce the outcome of  'proc sort nodupkey' . You lost variables. And if this is what you want, there is simpler SQL way of doing it, just use 'distinct'.

	proc sql;
	create table news as
	select distinct usubjid, visit from new
	order by usubjid, visit;
	quit;

Haikuo

[michtka]

Thanks.

V.