Warm tip: This article is reproduced from stackoverflow.com, please click
sas select sql syntax

In SAS, how can I select all the ID groups which has specific relationship between another variables

发布于 2020-04-13 10:37:21

For example, I want to get dataset2 from dataset1.

From dataset1, all IDs, whose value1 of any specific phase is over 10 points greater than the value2 of a previous phase within the IDs (pointed as arrow), were selected in dataset2.

Dataset1

Dataset2

I am using SAS EG version and it was impossible for me to make such query.

Thank you very much in advance.

Questioner
Dream Sylph
Viewed
43
分享
Gordon Linoff 2020-02-04 00:22

You can do this in SQL. To get the rows matching the condition:

select t.*
from t join
     t tnext
     on tnext.id = t.id and 
        tnext.phase = t.phase + 1
where tnext.value1 > t.value2 + 10;

Then you can list the ids using in or exists:

select t.*
from t
where t.id in (select t2.id
               from t t2 join
                    t tnext
                    on tnext.id = t2.id and 
                       tnext.phase = t2.phase + 1
               where tnext.value1 > t2.value2 + 10
              );
Dream Sylph 2020-02-05 06:44:56

Thank you!! With your help I succeeded in selecting the group which I wanted. I also came to know about where-in and join-on clauses.

Related issues

1
Query is possibly slow?
2
Simple Sql Role query issue
3
Group by query with rollup
4
How to include a PHP variable inside a MySQL statement
5
Getting empty rows from "SELECT ALL" query in PostgreSQL
6
MySQL pivot table using java
7
Implement smart search by text field in database
8
How to speed up count(distinct) with Between clause in MySQL
9
how to perform sql query MIN/MAX FILTER
10
Dynamic pivot in oracle sql