proc sort nodupkey equivalent proc sql

[michtka]

Hi guys,

there some equivalentt to proc sort nodupkey using proc sql?

For example, if I want to remove the next record of a dataset (have):

subjid prefterm treat day_start_study

1         drug1     placebo   -1

2        drug2     placebo    -12

2       drug2       placebo   -12

3      drug2        placebo   -11

to remove the duplicate record number 3, I use:

proc sort data=have out=want nodupkey;

by subjid prefterm treat day_start_study;

run;

1         drug1     placebo   -1

2        drug2     placebo    -12

3      drug2        placebo   -11

Now , using proc freq I obtain the frequency of the prefterm by treat:

proc freq data=want noprint;

tables treat*prefterm/ out=wantf (drop=percent);

run;

dataset wantf:

  treat   prefterm  count

placebo  drug1   1

placebo drug2   2

and using proc transpose:

proc transpose data=wantf out=wantft;

by prefterm;

id treat;

var count;

run;

I obtain the dataset wantft:

prefterm   placebo 

drug1        1

drug2        2

Can anyone do it using proc sql?

Thanks in advance.

J V

[FriedEgg · Accepted solution]

data have;
 input subjid (prefterm treat) ($) day_start_study;
 cards;
1 drug1 placebo          -1
2 drug2 placebo          -12
2 drug2 placebo          -12
3 drug2 placebo -11
;
run;
proc sql;
 /* proc sort data=have out=_have nodupkey; by subjid prefterm treat day_start_study; run; */
 create view _have as
 select subjid,prefterm,treat,day_start_study,count(*)
   from have
  group by subjid,prefterm,treat,day_start_study;
 select distinct cat('sum(treat="',treat,'") as ',treat)
   into :treat_counts separated by ', '
   from have;
 select prefterm,&treat_counts
   from _have
  group by prefterm;
quit;
prefterm   placebo
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
drug1            1
drug2            2

[michtka]

Thank you very much,

it works perfectly, but I find it bit complex, still thinking of keep going with proc sort nodupkey

until i understand more proc sql.

Regards.

[michtka]

/* proc sort data=have out=_have nodupkey; by subjid prefterm treat day_start_study; run; */

equivalent to:

proc sql;

create view _have as
select subjid,prefterm,treat,day_start_study,count(*)
   from have
  group by subjid,prefterm,treat,day_start_study;

quit;

Thanks.

[Ksharp]

SQL is more succinct .

data have;
 input subjid (prefterm treat) ($) day_start_study;
 cards;
1 drug1 placebo          -1
2 drug2 placebo          -12
2 drug2 placebo          -12
3 drug2 placebo -11
;
run;
proc sql;
 select prefterm,count(treat='placebo') as placebo
  from (select distinct * from have)
   group by prefterm ;
quit;

Ksharp

[FriedEgg]

KSharp,

Good point, select distinct * will provide the same results.

Your count function needs to be a sum though.

select prefterm,sum(treat='placebo') as placebo

  from (select distinct * from have)

   group by prefterm ;

[Ksharp]

Opps.

It is my typo. Thank you Matt to point out.

Ksharp

[michtka]

Hi guys, I dont know about using the proc qsl for the structure I wanted:

proc sort nodupkey

proc frequency

proc transpose

I understand it looks like more code programming, but it is more intuitive to me

Thanks anyway for the discussion.