BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michtka
Fluorite | Level 6

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;


1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

3 REPLIES 3
michtka
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

Thanks.

V.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1258 views
  • 3 likes
  • 2 in conversation