Databases
StackOverflow2013
Postsdbo
POFind the maximum output over a variable-length period
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFind the maximum output over a variable-length period
    primarykey
    data
    text
    <p>I have a hypothetical set of data with 3 columns that has monthly profit data for a set of widget machines. I am trying to figure out the maximum profit period within a 2-year span.</p> <p>The 3 columns are as follows:<br> <strong>name</strong>: identifier of widget machine (there are maybe 100 of these)<br> <strong>date</strong>: month/year over a 2 year period<br> <strong>profit</strong>: dollars made from widgets that month (can be negative if costs exceed revenue)</p> <p>The <em>maximum profit period</em> is a concurrent set of months at least 3 months long (and could encompass all of the data).</p> <p>Obviously I could brute force this and simply test every combination: Jan-Mar, Jan-Apr, Jan-May, Feb-Apr, etc. but I am looking for a better solution than creating all of these by hand. It seems like the data is a bit too big to want to transpose across and turn months into columns so I would like to be able to operate on a stacked dataset as described.</p> <p>I'd prefer a sas data step but an sql query that works in proc SQL would be fine as well (but the sets of subqueries that might be required are beyond my ability).</p> <p><strong>Example Data:</strong></p> <pre><code>data max(drop=dt); length name dt $50; infile datalines delimiter=','; input name $ dt profit; date=input(dt,mmddyy10.); format date mmddyy10.; datalines; Widget1,01/01/2011,1000 Widget1,02/01/2011,2000 Widget1,03/01/2011,500 Widget2,01/01/2011,100 Widget2,02/01/2011,200 Widget2,03/01/2011,-50 Widget2,04/01/2011,250 Widget2,05/01/2011,-150 Widget2,06/01/2011,-250 Widget2,07/01/2011,400 Widget2,08/01/2011,0 Widget2,03/01/2011,-200 ; </code></pre> <p>Maybe a better phrasing of the question would be "How do I come up with all possible consecutive combinations of values?" From a query like that, I could then take the max of combinations where # of values >= 3.</p> <p>The query would build up every combination of sequential rows in the table, drop those where there are less than 3 rows, and then return the max value (grouped by Widget# of course). I suppose it would also helpful to know the starting and ending row for each combination. I'm trying to work out how this would be done in an SQL query (doesn't sound like a sas datastep to my mind)</p> <p><strong>Python Sample:</strong><br> Here is a sample with some made up data that I wrote in Python. It is not the most efficient thing but it gets the sort of result I am looking for--I just can't figure out how to replicate it in SQL or SAS:</p> <pre><code>from itertools import groupby data = [] data.append(['Widget1','Jan',5]) data.append(['Widget1','Feb',1]) data.append(['Widget1','Mar',-2]) data.append(['Widget1','Apr',0]) data.append(['Widget1','May',-3]) data.append(['Widget1','Jun',8]) data.append(['Widget1','Jul',-2]) data.append(['Widget1','Aug',1]) data.append(['Widget2','Jan',-1]) data.append(['Widget2','Feb',1]) data.append(['Widget2','Mar',-3]) data.append(['Widget2','Apr',1]) data.append(['Widget2','May',-60]) data.append(['Widget2','Jun',9]) data.append(['Widget2','Jul',-2]) data.append(['Widget2','Aug',20]) results = [] for key, group in groupby(data, lambda g: g[0]): max = -999999 for i,v in enumerate(data): if key &lt;&gt; v[0]: continue runningtotal = 0 for j,w in enumerate(data): if key &lt;&gt; w[0]: continue if i &lt;= j: runningtotal = runningtotal + w[2] if i+2 &lt;= j and runningtotal &gt; max: max = runningtotal maxstart = v[1] maxend = w[1] results.append([key, maxstart, maxend, max]) print results </code></pre> <p>This gives me the result of [['Widget1', 'Jan', 'Jun', 9], ['Widget2', 'Jun', 'Aug', 27]] for the fake python data I made.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJason Sundram
    1. USorh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFind the maximum output over a variable-length period
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFind the maximum output over a variable-length period
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POFind the maximum output over a variable-length period
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COWhich database (Oracle, SQLServer, MySQL etc.) are you using? (Date arithmetic functions/operators vary between platforms, so this will affect the answer.) Also, is the date field populated with the first, last, median or random day of the month?
      singulars
      1. POFind the maximum output over a variable-length period
      1. USJosef K
    2. COI'm using a sas dataset with proc SQL (in my experience it behaves pretty close to mysql...there are differences but most SQL queries work fine). Date field has the first day of the month.
      singulars
      1. POFind the maximum output over a variable-length period
      1. USorh
    3. COI added the beginning of some sample data...could you adjust to make it more accurate. Also, if you could use the sample to point out an example result. This goes a long way toward helping us help you.
      singulars
      1. POFind the maximum output over a variable-length period
      1. USJay Corbett
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload