By id function in proc sql

[TammyGoSAS]

Hello everyone,

Thank you for the experts' help in this community. I learn how to count how many flag=1 in previous 5 seconds for each record. The original data is:

ID      Time          Flag

1      8:45:01          0

2      8:45:04          1

3      8:45:06          1

4      8:45:08          0

5      8:45:09          1

6      8:45:11          0

7      8:45:13          0

Output:

ID      Time          Flag    Number

1      8:45:01          0           0

2      8:45:04          1           0

3      8:45:06          1           1

4      8:45:08          0           2

5      8:45:09          1           2

6      8:45:11          0           2

7      8:45:13          0           1

Code:

/*PROC SQL*/

PROC SQL;

  CREATE TABLE WANT AS

    SELECT *, (SELECT COALESCE(SUM(FLAG=1),0) FROM HAVE WHERE TIME BETWEEN A.TIME-5 AND A.TIME-1) AS NUMBER

        FROM HAVE A;

QUIT;     /** By Haikuo **/

To extend the question, what if there are multiple IDs in the data and treat each ID independently?

For example:

ID      Time          Flag

A      8:45:01          0

A      8:45:04          1

A      8:45:06          1

A      8:45:08          0

A      8:45:09          1

B      8:45:11          0

B      8:45:13          1

B      8:45:15          0

Desired output:

ID      Time          Flag    Number

A      8:45:01          0           0

A      8:45:04          1           0

A      8:45:06          1           1

A      8:45:08          0           2

A      8:45:09          1           2

B      8:45:11          0           0

B      8:45:13          1           0

B      8:45:15          0           1

I try to use group by id but it doesn't work. Is there any solution? Thank you so much for the help!!

Best,

Tammy

[bill0101 · Accepted solution]

This one is much easier - just further select the same ID within the where constraint.

Try this one:

data have;

     input ID $ Time hhmmss8. Flag;

     format time TIME8.0;

     cards;

A 8:45:01 0

A 8:45:04 1

A 8:45:06 1

A 8:45:08 0

A 8:45:09 1

B 8:45:11 0

B 8:45:13 1

B 8:45:15 0

;run;

proc sql;

     create table want_grouped as

     select A.*,

          (select COALESCE(SUM(FLAG=1),0)

           from have as B /*kind of self-join*/

           where A.ID=B.ID and A.TIME-5<=B.TIME<=A.TIME-1) as number

     from have as A

     order by A.ID, A.time;

quit;

This should give the desired output.

Bill

[TammyGoSAS]

Thank you so much Bill. It not only works well but also is flexible with more than one key variable to be matched.

[bill0101]

Yeah you're welcome. Here's even another equivalent way that does not require a nested sub-expression. You can choose the most efficient one for your data set.

By the way, did my solution of FIFO returns work for you? Please go to the question page and give some feedback about whether it works. Thanks.

Bill

proc sql;

create table want_grouped2 as

select distinct A.*, coalesce(SUM(B.FLAG),0) as number

from have as A

     left join have as B

           on A.ID=B.ID and A.TIME-5<=B.TIME<=A.TIME-1

     group by A.time

     order by A.ID, A.time;

quit;