change of numberic variable in each group ID from the earliest date to the latest.

[Alexxxxxxx]

Hi,

I expect to calculate the change of the 'scale' variable for each group ID from the earliest date to the latest.

in Table 1

ID	date	scale
1	05/09/2011	8
1	12/06/2011	8
1	07/30/2012	7
1	08/02/2013	7
1	03/06/2015	7
1	06/04/2015	9
1	08/02/2017	8
1	10/26/2017	8
2	03/17/2005	6
2	06/14/2006	6
2	12/26/2006	6
2	03/26/2007	5

1.all observations have been sorted by ID, and date variable,

2.I would like to know how does the 'scale' variable change date by date, (i.e., change=scale[n]-scale[n-1]),

3.the first observation (i.e., the earliest date in each ID group) is 0.

and finally, I expect to get the new variable 'change' in Table 2.

Table2

ID	date	scale	change
1	05/09/2011	8	0
1	12/06/2011	8	0
1	07/30/2012	7	-1
1	08/02/2013	7	0
1	03/06/2015	7	0
1	06/04/2015	9	2
1	08/02/2017	8	-1
1	10/26/2017	8	0
2	03/17/2005	6	0
2	06/14/2006	6	0
2	12/26/2006	6	0
2	03/26/2007	5	-1

Could you please give me some suggestions about this?

Thanks in advance.

data table1;
	infile cards dsd  dlm=",";
	input
	ID $
	edate :mmddyy10.
	number 8.
	;
	format
   edate mmddyy10.
   ;
	cards;
1,05/09/2011,8
1,12/06/2011,8
1,07/30/2012,7
1,08/02/2013,7
1,03/06/2015,7
1,06/04/2015,9
1,08/02/2017,8
1,10/26/2017,8
2,03/17/2005,6
2,06/14/2006,6
2,12/26/2006,6
2,03/26/2007,5

	;;;;
run;

data want;
length number 8.;
set table1;
by ID;
if first.ID then number=0

[novinosrin · Accepted solution]

data table1;
	infile cards dsd  dlm=",";
	input
	ID $
	edate :mmddyy10.
	number 8.
	;
	format
   edate mmddyy10.
   ;
	cards;
1,05/09/2011,8
1,12/06/2011,8
1,07/30/2012,7
1,08/02/2013,7
1,03/06/2015,7
1,06/04/2015,9
1,08/02/2017,8
1,10/26/2017,8
2,03/17/2005,6
2,06/14/2006,6
2,12/26/2006,6
2,03/26/2007,5
;;;;
run;

data want;
 set table1;
 by id;
 change=dif(number);
 if first.id then change=0;
run;