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
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
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
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.
/* 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.
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
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 ;
Opps.
It is my typo. Thank you Matt to point out.
Ksharp
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.