BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Plessein
Fluorite | Level 6

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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;

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Plessein
Fluorite | Level 6

Thank you for your prompt answer.

 

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

 

To add some explanation,

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.

 

I appreciate your help.

Astounding
PROC Star

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

Shmuel
Garnet | Level 18

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;

Plessein
Fluorite | Level 6

Thank you so much.

 

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

Plessein
Fluorite | Level 6

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)

ballardw
Super User

@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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 977 views
  • 1 like
  • 5 in conversation