## DATA Step, Macro, Functions and more

Solved
Contributor
Posts: 26

[ Edited ]

Hello,

I am trying to work with a sample data about people's visit to a certain state and sort out whether each person has visited

the capital (Washington D.C ) or not and how many times each person visited there by flight.

The major problem I am having is grouping with the new variables.

Below is my sample data .

ID= personal identification number

FLIGHT_TICKET_N= flight ticket number

STATE = state in the U.S (DC=Washington D.C, captial city of the U.S)

data VISIT;
input ID\$ FLIGHT_TICKET_N STATE\$;
cards;
10063 1111 DC
10063 1112 DC
10063 1113 DC
10073 2221 DC
10073 2223 TX
10083 3331 CA
10083 3332 WA
run;

data visit;
set visit;
captial=".";
if substr(STATE,1,2) in ("DC") then captial=1;
if captial ~= 1 then captial=0;
run;

data visit_1;
set visit;
by ID;
if first.ID then acc_capital=0;
acc_capital + captial;
run;

so far it works as I intended. However, when I tried this:

proc sort data=visit_1 out=visit_2 nodupkey; by ID; run;

The results do not correctly reflect:

#1. whether each person has visited the capital city or not

#2. the accumulated number of visit to the capital city for each person.

as shown in the picture.

I really need to figure this out.

I am I would appreciate any help.

Accepted Solutions
Solution
‎06-19-2016 09:26 PM
Super User
Posts: 10,280

[ Edited ]

Sort with nodupkey only removes any records with duplicate keys after encountering the first on, and does not help in accumulating values.

Other procedures are there for this:

``````data VISIT;
input ID\$ FLIGHT_TICKET_N STATE\$;
cards;
10063 1111 DC
10063 1112 DC
10063 1113 DC
10073 2221 DC
10073 2223 TX
10083 3331 CA
10083 3332 WA
;
run;

data visit;
set visit;
if substr(STATE,1,2) in ("DC")
then capital = 1;
else capital = 0;
run;

proc summary data=visit;
by id;
var capital;
output
out=want (drop=_type_ _freq_)
max(capital) = capital
sum(capital) = acc_capital
;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎06-19-2016 09:26 PM
Super User
Posts: 10,280

[ Edited ]

Sort with nodupkey only removes any records with duplicate keys after encountering the first on, and does not help in accumulating values.

Other procedures are there for this:

``````data VISIT;
input ID\$ FLIGHT_TICKET_N STATE\$;
cards;
10063 1111 DC
10063 1112 DC
10063 1113 DC
10073 2221 DC
10073 2223 TX
10083 3331 CA
10083 3332 WA
;
run;

data visit;
set visit;
if substr(STATE,1,2) in ("DC")
then capital = 1;
else capital = 0;
run;

proc summary data=visit;
by id;
var capital;
output
out=want (drop=_type_ _freq_)
max(capital) = capital
sum(capital) = acc_capital
;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 26

Dear KurtBremser,

Thank you very much for your help and support.

🔒 This topic is solved and locked.

Discussion stats
• 2 replies
• 249 views
• 0 likes
• 2 in conversation