首页 > 解决方案 > Dividing within a column based on other columns matching

问题描述

I am trying to calculate the current measurement in column “Total" minus the lowest measurement previously recorded in column "Total" where the current measurement in column “Total” corresponding to the value in column "Trade" is less than(<) the minimum measurement in column “Total” corresponding to the value in column "trade", and if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. To emphasize, the minimum value must be a previously recorded. If the measurement is less than the current measurement but was not recorded previously (according to the “date” column), it does not quality to be subtracted from the current measurement. An example of the output is provided below.

data Have;
    input Subject Type :$12. Date &:anydtdte. Trade Procedure :$12. Measurement;
    format date yymmdd10.;
    datalines;

Subject Type      Date                Trade                Procedure     Total      
500   Initial    15 AUG 2017            6                   Invasive        20        
500   Initial    15 AUG 2017            9                   Surface         35        
500   Followup   15 AUG 2018            8                   Invasive        54       
428   Followup    15 AUG 2018          56                   Outer           29        
765   Seventh     3 AUG 2018           12                   Other           13     
500   Followup    3 JUL 2018           23                   surface         98    
428   Initial     3 JUL 2017           34                   Outer           10    
765   Initial     20 JUL 2019          4                    Other           19     
610   Third       20 AUG 2019         58                    Invasive        66   
610   Initial     17 Mar 2018         25                    Invasive        17     

*Example of Output;

Subject Type      Date                Trade                Procedure     Total        Output
500   Initial    15 AUG 2017            6                   Invasive        20        20/20
500   Initial    15 AUG 2017            9                   Surface         35        35/35
500   Followup   15 AUG 2018            8                   Invasive        54        54/20
428   Followup    15 AUG 2018          56                   Outer           29       29/10
765   Seventh     3 AUG 2018           12                   Other           13       13/19
500   Followup    3 JUL 2018           23                   surface         98       98/35
428   Initial     3 JUL 2017           34                   Outer           10       10/10
765   Initial     20 JUL 2019           4                   Other           19       19/19
610   Third       20 AUG 2019          58                   Invasive        66       66/17
610   Initial     17 Mar 2018          25                   Invasive        17       17/17

标签: datatablesasdatasetdivisionproc-sql

解决方案


不确定,但这是我能得到的最接近你的输出的东西这是否符合您想要的输出?

我做了一个monotonic()变量,然后按SUBJECTPROCEDURE变量对其进行排序。然后使用条件将表连接到自身t1.rank_monotonic +1 = t2.rank_monotonic


推荐阅读