DATA Step, Macro, Functions and more

please help: NODUPKEY does not accumulate values

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

please help: NODUPKEY does not accumulate values

[ 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.

 

Thank you in advance. 


sasq1.jpg

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

Re: please help: NODUPKEY does not accumulate values

[ Edited ]
Posted in reply to sasworker16

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

View solution in original post


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

Re: please help: NODUPKEY does not accumulate values

[ Edited ]
Posted in reply to sasworker16

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
Contributor
Posts: 24

Re: please help: NODUPKEY does not accumulate values

Posted in reply to KurtBremser

Dear KurtBremser,

 

Thank you very much for your help and support.

 

 

☑ This topic is solved.

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

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