Report generation

[Eason]

What is the syntax for SAS to automatically generate a report like Table.1 by pulling information from Table.2

Active Date: The date that the scoreA first time is not "0" for each site. (in this case, the active date is 1/4/2017 for site A, service C)

Active Date2: The date that the scoreA first time exceed 30 for each site. (in this case, the active date2 is 1/6/2017 for site A, service C)

ScoreC: the value of scoreB that is three days prior active date. (in this case, scoreC is 1 for site A, serviceC)

ScoreD: the value of score B that is three days after active date. ( in this case, scoreD is 4 for site A, serviceC)

ScoreE: the value of scoreB that is three days prior active date2. (in this case, scoreE is 0 for site A, serviceC)

ScoreF: the value of scoreB that is three days after active date2. (in this case, scoreF is 2 for site A, serviceC)

I want the program could automatically redo for each site by this two types of service, cuz I'm dealing with hundreds different sites. 

If there is no three days after active date1/active date2 (sometimes it just has two days or one day) I will take the scoreD/scoreF of the date that is the closest to the third date. (in this case, there is only two days after active date2 for siteA serviceD, so I will take 6 as my scoreF)

Thank you 

Table.1

Site	Service	Active Date	ScoreC	ScoreD	Active Date2	ScoreE	ScoreF
A	C
A	D
B	C
B	D

 Table.2

Date	Site	Service	ScoreA	ScoreB
1/1/2017	A	C	0	1
1/2/2017	A	C	0	1
1/3/2017	A	C	0	0
1/4/2017	A	C	2	0
1/5/2017	A	C	5	2
1/6/2017	A	C	31	3
1/7/2017	A	C	18	4
1/8/2017	A	C	40	1
1/8/2017	A	C	50	2
1/8/2017	A	D	0	7
1/9/2017	A	D	0	0
1/10/2017	A	D	0	2
1/11/2017	A	D	7	1
1/12/2017	A	D	33	4
1/13/2017	A	D	24	5
1/14/2017	A	D	41	6
1/15/2017	B	C	0	4
1/16/2017	B	C	0	1
1/17/2017	B	C	0	2
1/18/2017	B	C	18	3
1/19/2017	B	C	32	9
1/20/2017	B	C	19	0
1/21/2017	B	C	36	1
1/21/2017	B	C	48	2
1/22/2017	B	D	0	2
1/23/2017	B	D	0	6
1/24/2017	B	D	0	7
1/25/2017	B	D	26	9
1/26/2017	B	D	41	4
1/27/2017	B	D	34	2
1/28/2017	B	D	57	5

[Reeza · Accepted solution]

What do you have so far? Can you provide your data as a data step rather than text (instructions here)? 

Will you always have a record for every single day and only a single record?

Assuming the answer to the last question is Yes, then look into the LAG function. 

Combined with RETAIN and FIRST/LAST BY group processing this is relatively straightforward. 

[Eason]

Thank you for your reply. My data is a single record csv file which contains more than 20,000 observations. It belongs my employer, that's why I made up this two tables. Your answer is very helpful, someone else told me the same way to handle this problem. Thank you.