Help using Base SAS procedures

proc sort nodupkey equivalent proc sql

Accepted Solution Solved
Reply
Super Contributor
Posts: 301
Accepted Solution

proc sort nodupkey equivalent proc sql

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


Accepted Solutions
Solution
‎03-28-2012 09:08 PM
Trusted Advisor
Posts: 1,300

Re: proc sort nodupkey equivalent proc sql

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

View solution in original post


All Replies
Solution
‎03-28-2012 09:08 PM
Trusted Advisor
Posts: 1,300

Re: proc sort nodupkey equivalent proc sql

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

Super Contributor
Posts: 301

Re: proc sort nodupkey equivalent proc sql

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.

Super Contributor
Posts: 301

Re: proc sort nodupkey equivalent proc sql

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

Super User
Posts: 9,662

Re: proc sort nodupkey equivalent proc sql

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

Trusted Advisor
Posts: 1,300

proc sort nodupkey equivalent proc sql

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 ;

Super User
Posts: 9,662

proc sort nodupkey equivalent proc sql

Opps.

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

Ksharp

Super Contributor
Posts: 301

Re: proc sort nodupkey equivalent proc sql

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 Smiley Happy

Thanks anyway for the discussion.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 4380 views
  • 0 likes
  • 3 in conversation