topic By id function in proc sql in SAS Programming

By id function in proc sql

TammyGoSAS — Wed, 12 Feb 2014 04:25:28 GMT

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

Re: By id function in proc sql

bill0101 — Wed, 12 Feb 2014 11:05:56 GMT

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

Re: By id function in proc sql

TammyGoSAS — Thu, 13 Feb 2014 06:46:38 GMT

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

Re: By id function in proc sql

bill0101 — Thu, 13 Feb 2014 08:36:50 GMT

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;