DATA Step, Macro, Functions and more

Report generation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Report generation

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

SiteServiceActive DateScoreCScoreDActive Date2ScoreEScoreF
AC      
AD      
BC      
BD      

 Table.2

DateSiteServiceScoreAScoreB
1/1/2017AC01
1/2/2017AC01
1/3/2017AC00
1/4/2017AC20
1/5/2017AC52
1/6/2017AC313
1/7/2017AC184
1/8/2017AC401
1/8/2017AC502
1/8/2017AD07
1/9/2017AD00
1/10/2017AD02
1/11/2017AD71
1/12/2017AD334
1/13/2017AD245
1/14/2017AD416
1/15/2017BC04
1/16/2017BC01
1/17/2017BC02
1/18/2017BC183
1/19/2017BC329
1/20/2017BC190
1/21/2017BC361
1/21/2017BC482
1/22/2017BD02
1/23/2017BD06
1/24/2017BD07
1/25/2017BD269
1/26/2017BD414
1/27/2017BD342
1/28/2017BD575

Accepted Solutions
Solution
‎01-10-2018 04:11 PM
Super User
Posts: 23,293

Re: Report generation

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. 

 

 

 

 

 

 

View solution in original post


All Replies
Solution
‎01-10-2018 04:11 PM
Super User
Posts: 23,293

Re: Report generation

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. 

 

 

 

 

 

 

Occasional Contributor
Posts: 5

Re: Report generation

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 187 views
  • 0 likes
  • 2 in conversation