how to get second transcation of guest

[NagendraBS]

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

[mohamed_zaki]

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;

[Patrick]

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;

[Loko]

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;

[Ksharp]

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

[stat_sas]

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;

[data_null__]

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 print;
   run;
proc summary data=test nway;
   class guest_id;
   output out=second(drop=_type_ tran_id_1 amount_1)
      idgroup(min(tran_id) out[2](tran_id amount)=);
   run;
proc print;
   run;

[art297]

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;

[Ksharp]

Hi. Arthur.T ,

What if guest_id was character type variable ?

[art297]

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?