DATA Step, Macro, Functions and more

please help: NODUPKEY does not accumulate values

Accepted Solution Solved
Reply
Contributor
Posts: 23
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: 6,928

Re: please help: NODUPKEY does not accumulate values

[ 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

View solution in original post


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

Re: please help: NODUPKEY does not accumulate values

[ 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
Contributor
Posts: 23

Re: please help: NODUPKEY does not accumulate values

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
  • 196 views
  • 0 likes
  • 2 in conversation