Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Find out the how much a person spent in the last 100 days,

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-10-2020 02:14 PM
(1308 views)

Good morning,

I have observations of thousands of individuals (variable X for individuals a, b, c, d.. ) overtime (d) and the money each individual spent on that date (Y). The data are sorted by X and d

**data** k;

input X d y ;

cards ;

a 1/1/1996 5.1

a 10/27/1996 2.3

a 12/16/1996 8.1

a 1/5/1997 9.6

a 1/11/1997 1.6

b 2/3/1997 6.2

b 3/25/1997 5.8

b 11/15/1998 2.6

b 1/14/1999 4.9

b 4/14/1999 3.7

b 5/7/1999 4.6

b 5/7/1999 2.3

c 5/13/1999 9.6

I am trying to resolve two questions:

- How many times one individual spent money in a period of 100 days? (this is the variable “times” in the solution below)
- How much money has she/he spent in a the last 100 days? (this is “sum” in the solutions below.

So the question is to find out “times” and “sum”: the times an individual appears in the data base in the last 100 days, and the money spent in the last by this individual in the last 100 days.

The solution in this case is below (and in the attached file). Diff is the number of days between two dates (I did this to get to the solution in excel; it may not be necessary). This difference is -999 when we change to a different individual.

In this case the solution is:

x | d | y | diff | times | sum |

a | 1/1/1996 | 5.1 | -999 | 1 | 5.1 |

a | 10/27/1996 | 2.3 | 300 | 1 | 2.3 |

a | 12/16/1996 | 8.1 | 50 | 1 | 10.4 |

a | 1/5/1997 | 9.6 | 20 | 2 | 20 |

a | 1/11/1997 | 1.6 | 6 | 3 | 21.6 |

b | 2/3/1997 | 6.2 | -999 | 1 | 6.2 |

b | 3/25/1997 | 5.8 | 50 | 2 | 12 |

b | 11/15/1998 | 2.6 | 600 | 1 | 2.6 |

b | 1/14/1999 | 4.9 | 60 | 1 | 7.5 |

b | 1/29/1999 | 3.7 | 15 | 2 | 11.2 |

b | 3/10/1999 | 4.6 | 40 | 3 | 8.3 |

b | 3/10/1999 | 2.3 | 0 | 4 | 10.6 |

c | 3/16/1999 | 9.6 | -999 | 1 | 9.6 |

Thank you for your help,

Tomas

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If you don't have a big table.

```
data k;
input X $ d y ;
informat d mmddyy8. ;
cards ;
a 01/01/96 5.1
a 10/27/96 2.3
a 12/16/96 8.1
a 01/05/97 9.6
a 01/11/97 1.6
b 02/03/97 6.2
b 03/25/97 5.8
b 11/15/98 2.6
b 01/14/99 4.9
b 01/29/99 3.7
b 03/10/99 4.6
b 03/10/99 2.3
c 03/16/99 9.6
;
proc sql;
create table want as
select *,
(select count(*) from k where x=a.x and d between a.d-100 and a.d) as times,
(select sum(y) from k where x=a.x and d between a.d-100 and a.d) as sum
from k as a;
quit;
```

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Please do a test of your data step code, and fix it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Sorry Kurt:

data k;

input X $ d y ;

informat d mmddyy8. ;

cards ;

a 01/01/96 5.1

a 10/27/96 2.3

a 12/16/96 8.1

a 01/05/97 9.6

a 01/11/97 1.6

b 02/03/97 6.2

b 03/25/97 5.8

b 11/15/98 2.6

b 01/14/99 4.9

b 01/29/99 3.7

b 03/10/99 4.6

b 03/10/99 2.3

c 03/16/99 9.6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

data k;

input X $ d y ;

informat d mmddyy8. ;

cards ;

a 01/01/96 5.1

a 10/27/96 2.3

a 12/16/96 8.1

a 01/05/97 9.6

a 01/11/97 1.6

b 02/03/97 6.2

b 03/25/97 5.8

b 11/15/98 2.6

b 01/14/99 4.9

b 01/29/99 3.7

b 03/10/99 4.6

b 03/10/99 2.3

c 03/16/99 9.6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

How do you expect the "last 100 days" to be specified? From a given date, the date the program runs or something else?

A where clause with the start and end dates will subset the data and counts + sums points towards Proc Means or Summary with a Class or By statement with the individual identification variable(s)

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If you don't have a big table.

```
data k;
input X $ d y ;
informat d mmddyy8. ;
cards ;
a 01/01/96 5.1
a 10/27/96 2.3
a 12/16/96 8.1
a 01/05/97 9.6
a 01/11/97 1.6
b 02/03/97 6.2
b 03/25/97 5.8
b 11/15/98 2.6
b 01/14/99 4.9
b 01/29/99 3.7
b 03/10/99 4.6
b 03/10/99 2.3
c 03/16/99 9.6
;
proc sql;
create table want as
select *,
(select count(*) from k where x=a.x and d between a.d-100 and a.d) as times,
(select sum(y) from k where x=a.x and d between a.d-100 and a.d) as sum
from k as a;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you ,

I appreciate your help. It is simple and it works perfectly.

Thank you again for your time.

Tomas

I appreciate your help. It is simple and it works perfectly.

Thank you again for your time.

Tomas

Are you ready for the spotlight? We're accepting content ideas for **SAS Innovate 2025** to be held May 6-9 in Orlando, FL. The call is **open **until September 25. Read more here about **why** you should contribute and **what is in it** for you!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.