By id function in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

By id function in proc sql

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


Accepted Solutions
Solution
‎02-12-2014 06:05 AM
Occasional Contributor
Posts: 17

Re: By id function in proc sql

Posted in reply to TammyGoSAS

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

View solution in original post


All Replies
Solution
‎02-12-2014 06:05 AM
Occasional Contributor
Posts: 17

Re: By id function in proc sql

Posted in reply to TammyGoSAS

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

Occasional Contributor
Posts: 15

Re: By id function in proc sql


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

Occasional Contributor
Posts: 17

Re: By id function in proc sql

Posted in reply to TammyGoSAS

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 232 views
  • 0 likes
  • 2 in conversation