topic Re: Sorting and flaging transactions. in SAS Programming

Sorting and flaging transactions.

omerzeybek — Mon, 28 Sep 2015 11:39:23 GMT

Hi I am trying to prepare a transactional data. I have Individuals and their transaction dates. I want to sort data primarly on Customer ID and then tranaction date. Until there it is not a big challenge, but in addition to this i require to print a new variable that states oder of every transaction by number for every individual. The final data would look like the picture i attached bellow.

Thank you very much

Re: Sorting and flaging transactions.

PaigeMiller — Mon, 28 Sep 2015 13:33:25 GMT

proc sort data=original_data out=sorted_data;
    by id date;
run;
data sorted_data;
    set sorted_data;
    by id;
    if first.id then sort_tranx=0;
    sort_tranx+1;
run;

Re: Sorting and flaging transactions.

dcruik — Mon, 28 Sep 2015 14:28:17 GMT

The code below will do what PaigeMiller's code is doing, except this will give you the same value for Sort_tranx if the ID and Date are the same. Hope this helps!

data have;
input ID Date;
format Date mmddyy10.;
informat Date mmddyy10.;
datalines;
34532 01/01/2015
34532 02/03/2015
34532 05/06/2015
34532 01/01/2015
36978 01/02/2015
21562 01/01/2015
21562 01/02/2015
21562 01/03/2015
21562 01/04/2015
21562 01/05/2015
;
run;

proc sort data=have;
	by ID Date;
run;

data want;
  set have;
	by ID Date;
Date_lag=lag(Date);
	If First.ID then Sort_tranx=1;
	Else If First.ID=0 AND Date=Date_lag then do;
		Sort_tranx=Sort_tranx; end;
	Else Sort_tranx+1;
Drop Date_lag;
run;