topic Re: how to get second transcation of guest in SAS Procedures

how to get second transcation of guest

NagendraBS — Fri, 19 Dec 2014 07:30:21 GMT

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.

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

Re: how to get second transcation of guest

mohamed_zaki — Fri, 19 Dec 2014 08:03:29 GMT

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;

Re: how to get second transcation of guest

Patrick — Fri, 19 Dec 2014 10:35:42 GMT

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;

Re: how to get second transcation of guest

Loko — Fri, 19 Dec 2014 11:30:20 GMT

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;

Re: how to get second transcation of guest

Ksharp — Fri, 19 Dec 2014 12:03:55 GMT

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

Re: how to get second transcation of guest

stat_sas — Fri, 19 Dec 2014 13:47:40 GMT

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;

Re: how to get second transcation of guest

data_null__ — Fri, 19 Dec 2014 14:04:46 GMT

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;

Re: how to get second transcation of guest

art297 — Fri, 19 Dec 2014 15:22:55 GMT

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;

Re: how to get second transcation of guest

Ksharp — Sat, 20 Dec 2014 12:05:40 GMT

Hi. Arthur.T ,

What if guest_id was character type variable ?

Re: how to get second transcation of guest

art297 — Sat, 20 Dec 2014 14:43:56 GMT

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?