Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- General question on confirming records.

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sunday

All, first time here, please be gentle. Thanks.

I have a table of records. For each row, I have an existing variable, x, and a new variable y to be derived.

For each row, say with row index r, the new variable y is defined such that it is '1' if I can find a row afterwards, say row r+p, such that ALL the rows from row r to row r+p having the existing variable x larger than a threshold, say, 100, and it is '0' otherwise.

How can I do this in SAS? thanks, from a newbie.

Is it necessary to use a do-until loop here? or not necessary.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to philgo

Sunday

No loops required, but you may need a RETAIN, it depends exactly on what you're trying to do.

Can you please provide a small example that includes the data you start with and what you want as output. It does not need to be your actual data but should reflect the complexities, ie if you need to do this for multiple groups include at least two groups.

@philgo wrote:

All, first time here, please be gentle. Thanks.

I have a table of records. For each row, I have an existing variable, x, and a new variable y to be derived.

For each row, say with row index r, the new variable y is defined such that it is '1' if I can find a row afterwards, say row r+p, such that ALL the rows from row r to row r+p having the existing variable x larger than a threshold, say, 100, and it is '0' otherwise.

How can I do this in SAS? thanks, from a newbie.

Is it necessary to use a do-until loop here? or not necessary.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

Sunday

row index x (existing) y (to be derived)

--------------------------------------------------------------------

1 200 0 (because I cannot find any row afterwards such that all rows in between have x>100)

2 0 0

3 200 0

4 0 0

5 200 1 (row 5 and row 6 have x>100)

6 200 0

7 0 0

8 200 1 (row 8 and row 9 have x>100)

9 200 1 (row 9 and row 10 have x>100)

10 200 no definition

thanks a lot for the quick reply. please see the above sample data.

to derive the variable y for a row r, i need to look at all rows beyond and see if the condition saitisfies.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to philgo

Sunday - last edited Sunday

All you really seem to want is a dummy variable indicating whether both the current observation ("row") and the next observation have x>100. This is a ready-made problem for solution by merging a data set with itself, but with the simultaneously access records offset by 1 ("firstobs=2" below):

```
data have;
input r x;
datalines;
1 200
2 0
3 200
4 0
5 200
6 200
7 0
8 200
9 200
10 200
run;
data want (drop=_:);
merge have
have (firstobs=2 keep=x rename=(x=_x_next));
if _x_next=. then y=.;
else y=min(x,_x_next)>100;
run;
```

Just as you can merge two separate datasets you can to a self-merge, think of them as two parallel streams of data. In this case merging record 1 with record 2 of have, record 2 with 3, etc. But you can't have 2 variables named X at the same time, so X in the "firstobs=2" stream is renamed to _x_next.

The rest is logic. _x_next for the matching of record 10 has no record 11, so it is missing, allowing an easy test for arriving at the end of the merge.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

Sunday

Thanks very much. That helps a lot.