maybe multi-step if/then logic?

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

maybe multi-step if/then logic?

Hi, I have been working on reorganizing a SAS dataset that has many columns and rows.  I've made a dummy "dataset 1" here as an example.  Dummy "dataset 1":

patient          fruit          [many other columns]

patientA          apple          ...

patientA          banana          ...

patientA          mango          ...

patientB          apple          ...

patientC          cherry          ...

patientC          grape          ....

 

I have figured out how to create a new dataset containing only the entries that have fruit = apple.  I.e., "dataset 2":

patient          fruit          [many other columns]

patientA          apple          ...

patientB          apple          ....

 

I'm wondering if anyone could please teach me how to create a "dataset 3" from "dataset 1," that contains all of the entries for patients who have ever had fruit = apple.  In other words, I'm interested in examining a "dataset 3" that looks like this:

patient          fruit          [many other columns]

patientA          apple          ...

patientA          banana          ...

patientA          mango          ...

patientB          apple          ....

 

Thanks for your help and patience.


Accepted Solutions
Solution
‎01-25-2016 02:25 PM
Super Contributor
Posts: 490

Re: maybe multi-step if/then logic?

data have ;
input patient $ fruit $;
cards;
patientA apple
patientA banana
patientA mango
patientB apple
patientC cherry
patientC grape
;
run;

proc sql ;
create table applepatient as
select *
from have
where patient in(
select patient from have where fruit='apple') 
;
quit;

View solution in original post


All Replies
Solution
‎01-25-2016 02:25 PM
Super Contributor
Posts: 490

Re: maybe multi-step if/then logic?

data have ;
input patient $ fruit $;
cards;
patientA apple
patientA banana
patientA mango
patientB apple
patientC cherry
patientC grape
;
run;

proc sql ;
create table applepatient as
select *
from have
where patient in(
select patient from have where fruit='apple') 
;
quit;
Contributor
Posts: 25

Re: maybe multi-step if/then logic?

Thank you all!

Contributor
Posts: 25

Re: maybe multi-step if/then logic?

[ Edited ]

Is there a way to modify this program so that I only select a patient if fruit = apple at least twice (i.e., if there is >1 entry for this one patient in which fruit = apple)?

Super Contributor
Posts: 290

Re: maybe multi-step if/then logic?

If you have data set that is sortable by PATIENT then you can use a double SET statement. There are other ways but slightly complicated to deal with the unsorted data set. I believe that you don't need the complicated.

data have ;
input patient $ fruit $;
cards;
patientA apple
patientA banana
patientA mango
patientB apple
patientC cherry
patientC grape
;
run;

proc sort data = have;
by patient;
run;

%let fruitname = 'grape';
data want;
   do until(last.patient);
      set have;
      by patient;
      if fruit = &fruitname then found = 1;
   end;
   do until(last.patient);
      set have;
      by patient;
      if found then output;
   end;
drop found;
run;

The fruitname can be changed by changing the macro variable.  Within the PATIENT group, the occurrence of fruit can be in any row. All such records are selected out. For instance, the choice of 'grape' will pull out all records of patientC.

Super User
Posts: 9,867

Re: maybe multi-step if/then logic?

If I understand what you mean.

 

 

data have ;
input patient $ fruit $;
cards;
patientA apple
patientA banana
patientA mango
patientB apple
patientC cherry
patientC grape
;
run;

proc sql ;

select *
from have 
group by patient 
having sum(fruit='apple') gt 0 
;
quit;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 458 views
  • 2 likes
  • 4 in conversation