Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As an answer to your third question. The following approach seems simpler to me. And probably more performant. (since i have no joins)</p> <pre><code>/*For every (absolute) value, find how many more positive/negative occurrences we have per key*/ proc sql; create view V_INTERMEDIATE_VIEW as select key, abs(Value) as Value_abs, sum(sign(value)) as balance from INPUT_DATA group by key, Value_abs ; quit; *The balance variable here means how many times more often did we see the positive than the negative of this value. I.e., how many of either the positive or the negative were we not able to eliminate; /*Now output*/ data OUTPUT_DATA (keep=key Value); set V_INTERMEDIATE_VIEW; Value = sign(balance)*Value_abs; *Put the correct value back; do i=1 to abs(balance) by 1; output; end; run; </code></pre> <p><br> <br> <br> If you only want pure SAS (so no proc sql), you could do it as below. Note that the idea behind it remains the same.</p> <pre><code>data V_INTERMEDIATE_VIEW /view=V_INTERMEDIATE_VIEW; set INPUT_DATA; value_abs = abs(value); run; proc sort data=V_INTERMEDIATE_VIEW out=INTERMEDIATE_DATA; by key value_abs; *we will encounter the negatives of each value and then the positives; run; data OUTPUT_DATA (keep=key value); set INTERMEDIATE_DATA; by key value_abs; retain balance 0; balance = sum(balance,sign(value)); if last.value_abs then do; value = sign(balance)*value_abs; *set sign depending on what we have in excess; do i=1 to abs(balance) by 1; output; end; balance=0; *reset balance for next value_abs; end; run; </code></pre> <p>NOTE: thanks to Joe for some useful performance suggestions.</p>
    singulars
    1. This table or related slice is empty.
    1. POEliminate pairs of observations under the condition, that observations can have more than one possible partner observation
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmvherweg
    1. USmvherweg
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for your solution, it eliminates the pairs. But in productiv environment we got more columns and need to select them too. Sorry for not defining my problem in more detail. But your approach is also nice.
      singulars
      1. PO
      1. USMichael Kersten
    2. COThan you did something wrong. ;) I built a small test set to test most possible scenarios and it worked. I then took your code to create dataset test (replaced variable name alias with key and variable name amount with value) and tested that as well. For both solutions. And it works. :) After reading your comment, i just tested with your example at the top of your post (outside the code block) and that works too.
      singulars
      1. PO
      1. USmvherweg
    3. COYeah, I did. I used my_id as key, not alias. Shame on me xD First think twice, then post. Lesson learned :)
      singulars
      1. PO
      1. USMichael Kersten
 

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