topic please help: NODUPKEY does not accumulate values in SAS Programming

please help: NODUPKEY does not accumulate values

sasworker16 — Fri, 17 Jun 2016 10:56:12 GMT

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.

Re: please help: NODUPKEY does not accumulate values

Kurt_Bremser — Fri, 17 Jun 2016 11:13:02 GMT

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;

Re: please help: NODUPKEY does not accumulate values

sasworker16 — Mon, 20 Jun 2016 01:27:33 GMT

Dear KurtBremser,

Thank you very much for your help and support.