topic Re: proc sql instead of proc sort nodupkey in SAS Procedures

proc sql instead of proc sort nodupkey

michtka — Wed, 06 Jun 2012 14:00:51 GMT

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;

Re: proc sql instead of proc sort nodupkey

michtka — Wed, 06 Jun 2012 14:31:37 GMT

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;

Re: proc sql instead of proc sort nodupkey

Haikuo — Wed, 06 Jun 2012 14:40:35 GMT

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

Re: proc sql instead of proc sort nodupkey

michtka — Wed, 06 Jun 2012 14:47:39 GMT

Thanks.