DATA Step, Macro, Functions and more

Sorting and flaging transactions.

Reply
Contributor
Posts: 65

Sorting and flaging transactions.

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. 

 

 

Stack_Ques.png

 

Thank you very much 

Trusted Advisor
Posts: 1,615

Re: Sorting and flaging transactions.

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;

 

Frequent Contributor
Posts: 130

Re: Sorting and flaging transactions.

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;
Ask a Question
Discussion stats
  • 2 replies
  • 192 views
  • 1 like
  • 3 in conversation