data test;
input guest_id tran_id amount;
datalines
101 11230 499
102 11254 566
101 11245 699
103 11345 999
102 12145 799
103 11564 699
101 11897 245
102 12456 600
;
run
i need guest second transaction details only for each guest.
AS
101 11230 499
102 11254 566
101 11245 699------1
103 11345 999
102 12145 799------2
103 11564 699------3
101 11897 245
102 12456 600
This three records need to be as output
Please help me Out.
Thanks in advance..!
data test;
input guest_id tran_id amount;
datalines;
101 11230 499
102 11254 566
101 11245 699
103 11345 999
102 12145 799
103 11564 699
101 11897 245
102 12456 600
;
run;
proc sort data=test;
by guest_id tran_id;
run;
data new (drop=secondObsflag);
set test;
by guest_id;
retain secondObsflag;
if first.guest_id then secondObsflag=0;
if secondObsflag = 1 then output;
secondObsflag +1;
run;
Just as a code variation:
data test;
input guest_id tran_id amount;
datalines;
101 11230 499
102 11254 566
101 11245 699
103 11345 999
102 12145 799
103 11564 699
101 11897 245
102 12456 600
;
run;
proc sort data=test;
by guest_id tran_id;
run;
data new (drop=_:);
set test;
by guest_id;
_count+1;
if _count=2 then output;
if last.guest_id then _count=0;
run;
Hello,
With proc transpose:
data test;
input guest_id tran_id amount;
datalines;
101 11230 499
102 11254 566
101 11245 699
103 11345 999
102 12145 799
103 11564 699
101 11897 245
102 12456 600
;
run;
proc sort data=test out=testsorted;
by guest_id;
run;
proc transpose data=testsorted out=tr1;
by guest_id ;
var tran_id amount ;
run;
proc transpose data=tr1 out=want (drop=_name_);
by guest_id;
id _name_;
var col2;
run;
The simple way is proc sort . The efficient way is Hash Table.
data test; input guest_id tran_id amount; datalines; 101 11230 499 102 11254 566 101 11245 699 103 11345 999 102 12145 799 103 11564 699 101 11897 245 102 12456 600 ; run; proc sort data=test ;by guest_id;run; data want; set test; by guest_id; if first.guest_id then n=0; n+1; if n=2; run; data test; input guest_id tran_id amount; datalines; 101 11230 499 102 11254 566 101 11245 699 103 11345 999 102 12145 799 103 11564 699 101 11897 245 102 12456 600 ; run; data want1; if _n_ eq 1 then do; if 0 then set test; declare hash ha(); ha.definekey('guest_id') ; ha.definedata('n'); ha.definedone(); end; set test; if ha.find()=0 then do; n+1; ha.replace(); if n=2 then output; end; else do;n=1;ha.add();end; run;
Xia Keshan
proc sql;
create table want as
select * from
(select * from test group by guest_id having tran_id>min(tran_id))
group by guest_id
having tran_id=min(tran_id);
quit;
If your data are already sorted by tran_id, the following might be the fastest solution:
data test;
input guest_id tran_id amount;
datalines;
101 11230 499
102 11254 566
101 11245 699
103 11345 999
102 12145 799
103 11564 699
101 11897 245
102 12456 600
;
data want;
set test;
array allguests(9999999) _temporary_;
allguests(guest_id)+1;
if allguests(guest_id) eq 2 then output;
run;
Hi. Arthur.T ,
What if guest_id was character type variable ?
If it contains both alpha and numeric then, of course, my proposed code wouldn't work. If all of the values were numbers, though, simply including an input function would suffice.
I offered the code because I'm concerned about the limitations of the proc summary method. How many levels can it handle these days?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.