🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 08-14-2020 01:34 PM
(834 views)
Hi, I have a dataset that contains patients, visit and values. My confusion lies in the fact the data is ordered. I would like to be able to remove all the patients if one of the visits is "unscheduled" or is missing "second". This may have been asked before so feel free to direct me to a posted solution.
Have:
ID visit value
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
Want:
ID visit value
1 first 0
1 second 0
My pseudo attempt would be: if missing("second") or contains("unscheduled") delete ID.
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input ID visit :$12. value ;
cards;
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
;
proc sql;
create table want as
select *
from have
group by id
having not (max(visit='unscheduled')=1 or max(visit = 'second')=0);
quit;
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input ID visit $ value;
datalines;
ID visit value
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
;
input ID visit $ value;
datalines;
ID visit value
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input ID visit :$12. value ;
cards;
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
;
proc sql;
create table want as
select *
from have
group by id
having not (max(visit='unscheduled')=1 or max(visit = 'second')=0);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This is exactly what I was looking for. Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If the data are sorted by id:
data have;
input id visit :$11. value;
datalines;
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
run;
data want;
merge have (where=(visit='second') in=second_present)
have (where=(visit='unscheduled') in=unscheduled_present)
have;
by id;
if second_present=1 and unscheduled_present=0;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------