Use the sas analysis table, how to compare two lines of adjacent column data and do subtraction, and then the output, and bosses give directions

CodePudding user response:

Open the window there is a lag and lead lines of function, can obtain adjacent data

CodePudding user response:

Lag (" field value, "get up the field of the number of rows, there is no access to the fields after the return to the default value) over (partition by partition field value order by sorting the field values)
Lead (" field value ", get down the field the number of rows, there is no access to the fields after the return to the default value) over (partition by partition field value order by sorting the field values)
Using the above two built-in function can solve the field values in the same column,

Such as: data table table
Column (num
1
4

For the two rows of data subtraction
Select
Num - lag (num, 1, 0) over (order by num)
From the table.

The results for the
1
3

Interpret
For the computation of the first line up to find no Numbers, the default is 0, so 1-0=1
For the computation of the second line up for a line, number of 1, 4-1=3

Over to open the window function, over (open the window, each window of collation)
Open a window on the basis of
Distribute by
Partition by
Collation
Distribute by + sort by
Partition by + order by
Note: a over only corresponds to one aggregation function
The over clause cannot be used alone
1) used with aggregate functions, is each window aggregation
Aggregation function + over clause (distribute by)
O is the window of the whole aggregation
Aggregation function + over clause (distribute by + sort by)
O is by sorting data aggregation before the current
Execution sequence: distinct by the window - sort by a -- -- -- -- --
the aggregation function2) used with row_number
Add the line number in over the window

Look not to understand I can go to baidu once

CodePudding user response:

refer to the second floor downstairs little black big cousin's reply:

lag (" field value, "get up the field of the number of rows, there is no access to the fields after the return to the default value) over (partition by partition field value order by sorting the field values)
Lead (" field value ", get down the field the number of rows, there is no access to the fields after the return to the default value) over (partition by partition field value order by sorting the field values)
Using the above two built-in function can solve the field values in the same column,

Such as: data table table
Column (num
1
4

For the two rows of data subtraction
Select
Num - lag (num, 1, 0) over (order by num)
From the table.

The results for the
1
3

Interpret
For the computation of the first line up to find no Numbers, the default is 0, so 1-0=1
For the computation of the second line up for a line, number of 1, 4-1=3

Over to open the window function, over (open the window, each window of collation)
Open a window on the basis of
Distribute by
Partition by
Collation
Distribute by + sort by
Partition by + order by
Note: a over only corresponds to one aggregation function
The over clause cannot be used alone
1) used with aggregate functions, is each window aggregation
Aggregation function + over clause (distribute by)
O is the window of the whole aggregation
Aggregation function + over clause (distribute by + sort by)
O is by sorting data aggregation before the current
Execution sequence: distinct by the window - sort by a -- -- -- -- --
the aggregation function2) used with row_number
Add the line number in over the window

Look not to know I can go to baidu once

Thank you teacher! Very detailed!!!!!