Help using Base SAS procedures

how to get second transcation of guest

Reply
Occasional Contributor
Posts: 13

how to get second transcation of guest

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

Super Contributor
Posts: 490

Re: how to get second transcation of guest

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;

Respected Advisor
Posts: 3,887

Re: how to get second transcation of guest

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=_Smiley Happy;

  set test;

  by guest_id; 

  _count+1;

  if _count=2 then output;

  if last.guest_id then _count=0;

run;

Super Contributor
Posts: 305

Re: how to get second transcation of guest

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;

Super User
Posts: 9,662

Re: how to get second transcation of guest

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

Trusted Advisor
Posts: 1,204

Re: how to get second transcation of guest

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;

Respected Advisor
Posts: 3,777

Re: how to get second transcation of guest

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;

12-19-2014 8-03-16 AM.png
PROC Star
Posts: 7,356

Re: how to get second transcation of guest

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;

Super User
Posts: 9,662

Re: how to get second transcation of guest

Hi. Arthur.T ,

What if guest_id was character type variable ?

PROC Star
Posts: 7,356

Re: how to get second transcation of guest

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?

Ask a Question
Discussion stats
  • 9 replies
  • 353 views
  • 1 like
  • 8 in conversation