## Repeated IF/THEN conditions. Can I have more efficient way?

Solved
Occasional Contributor
Posts: 7

# Repeated IF/THEN conditions. Can I have more efficient way?

This is raw data ‘Tour’

 ID City Country Tour A Yes Yes A No No A Yes Yes B No No B Yes Yes C No No D Yes Yes

I would like to make my codes as following:

1. If people who had ID had visited city & country, I would like to check Tour=’Yes’ (I have to count Tour=‘Yes’)

2. I would like to delete the observation of which Tour = No if people have a record of ‘Tour=Yes’

3. If people had not visited any city & country, I would like to leave it as ‘Tour=No’

The following is what I want to get

 ID City Country Tour A Yes Yes Yes A Yes Yes Yes B Yes Yes Yes C No No No D Yes Yes Yes

I made my codes as follows

Proc sort data= work.Tour;

by ID;

Run;

Data work.Tour_I_want;

set work.Tour;

by ID;

if first.ID and last.ID then do;

if City = ‘Yes’ and Country = ‘Yes’ then Tour = 'Yes';

else  Tour = 'No';

end;

else if  last.ID=0 then do;

if City = ‘Yes’ and Country = ‘Yes’  then do;

Tour = 'Yes'; retain Tour; end;

else delete;

end;

else if last.ID then do;

if Tour = '1' then delete;

else if City = ‘Yes’ and Country = ‘Yes’ then Tour = '1';

else Tour = '0';

end;

Run;

This seems not efficient. I repeated the condition(City=’Yes’ and Country=’Yes’) three times.

Could you tell me the better way for this? Thank you in advance.

* I am using SAS university.

Accepted Solutions
Solution
‎12-07-2016 10:30 PM
Posts: 1,848

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

[ Edited ]

Next code gives the wanted result:

data have;
infile datalines;
input id \$ city \$ country \$;
datalines;
A Yes Yes
A No No
A Yes Yes
B No No
B Yes Yes
C No No
D Yes Yes
; run;

data want;
set have;
by ID;
length Tour \$3;
if city="Yes" and country="Yes"
then Tour="Yes";
else Tour="No";

if first.ID and last.ID
or Tour="Yes" then output;
run;

All Replies
Super User
Posts: 9,840

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

Unless I am mistaking your output, then a simple where clause can get you that output - note how I put test data as a datastep - this makes it easier for us to see your structure:

```data have;
input ID \$ City \$ Country \$;
datalines;
A Yes Yes
A No No
A Yes Yes
;
run;
data want;
set have (where=(city="Yes" and country="Yes"));
tour="Yes";
run;```
Occasional Contributor
Posts: 7

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

I think I didn't explain what I want exactly.

At first, I would like to select by ID.

If the ID has Tour = 'Yes'(s), it should be selected regardless of repeated number. (So, there could be 2 observation for A)

If the ID doesn't have Tour = 'Yes', it should be presented as Tour = 'No' (I am not selecting Tour='Yes' only)

If the observation has Tour='Yes' and Tour='No', I just need Tour = 'Yes' only.

Briefly speaking,

At least, per ID, there should be Tour = 'Yes' or Tour = 'No'. If it has Tour = 'Yes', I don't want the observation with Tour ='No'.

And, I should check the number of Tour ='Yes', so the observation with Tour='Yes' should be selected.

Super User
Posts: 6,934

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

What if City=No and Country=Yes?  What should happen then?

Solution
‎12-07-2016 10:30 PM
Posts: 1,848

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

[ Edited ]

Next code gives the wanted result:

data have;
infile datalines;
input id \$ city \$ country \$;
datalines;
A Yes Yes
A No No
A Yes Yes
B No No
B Yes Yes
C No No
D Yes Yes
; run;

data want;
set have;
by ID;
length Tour \$3;
if city="Yes" and country="Yes"
then Tour="Yes";
else Tour="No";

if first.ID and last.ID
or Tour="Yes" then output;
run;

Occasional Contributor
Posts: 7

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

Thank you so much.

It is great!. It works perfectly. Very efficient!

Occasional Contributor
Posts: 7

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

Sorry. it was unneccessary two variables. I could made it one.

Let's say that there is only (City=Yes and Country=Yes) or (City=No and Country = No)

Super User
Posts: 13,941

## Re: Repeated IF/THEN conditions. Can I have more efficient way?

Plessein wrote:

Sorry. it was unneccessary two variables. I could made it one.

Let's say that there is only (City=Yes and Country=Yes) or (City=No and Country = No)

And do what?

☑ This topic is solved.