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

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

7 REPLIES 7
FriedEgg
SAS Employee

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

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

/* 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
Super User

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
SAS Employee

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
Super User

Opps.

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

Ksharp

michtka
Fluorite | Level 6

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.

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
  • 7 replies
  • 9891 views
  • 0 likes
  • 3 in conversation