SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
serena13lee
Quartz | Level 8

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
novinosrin
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
serena13lee
Quartz | Level 8
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
;
novinosrin
Tourmaline | Level 20

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;
serena13lee
Quartz | Level 8
This is exactly what I was looking for. Thanks!
mkeintz
PROC Star

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

--------------------------

SAS Innovate 2025: Register Today!

 

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 835 views
  • 0 likes
  • 3 in conversation