topic Re: proc sort nodupkey equivalent proc sql in SAS Procedures

proc sort nodupkey equivalent proc sql

michtka — Thu, 29 Mar 2012 00:54:48 GMT

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

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

Re: proc sort nodupkey equivalent proc sql

FriedEgg — Thu, 29 Mar 2012 01:08:13 GMT

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

Re: proc sort nodupkey equivalent proc sql

michtka — Thu, 29 Mar 2012 01:44:30 GMT

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.

Re: proc sort nodupkey equivalent proc sql

michtka — Thu, 29 Mar 2012 02:02:54 GMT

/* 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.

Re: proc sort nodupkey equivalent proc sql

Ksharp — Thu, 29 Mar 2012 04:02:13 GMT

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

proc sort nodupkey equivalent proc sql

FriedEgg — Thu, 29 Mar 2012 05:20:35 GMT

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 ;

proc sort nodupkey equivalent proc sql

Ksharp — Thu, 29 Mar 2012 07:03:45 GMT

Opps.

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

Ksharp

Re: proc sort nodupkey equivalent proc sql

michtka — Thu, 29 Mar 2012 22:34:43 GMT

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.