turn on suggestions

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
- /
- retain with by statement

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-11-2015 10:01 AM

t

I believe that I should use a retain statement and a by statement i.e. by id but I am not sure how to program up what I want and I am Wondering if you have any insight.

I want if any occurrence for a given id has a certain value which we will call AP then all records for a given id have the value of AP for this id

Further if none of the records for a given id have value A but at least one has value DE then all records for a given id have the value of DE for this id

Further if none of the records for a given id have value AP or value DE then keep most recent ordered value by time (in the worked example, the last value, I have access to a time variable in the real data to assist in the sorting process).

data person;

input T_CLASS_NUM DECISIONTYPENEW $;

datalines;

1 DF

1 DF

1 AP

1 DF

2 DF

2 DE

2 AP

3 AP

4 DE

5 DF

5 DF

;

proc sort data=person; by T_CLASS_NUM; run;

data person2;

set person;

by T_CLASS_NUM;

retain flag2;

if first.T_CLASS_NUM then flag2 = .;

if DECISIONTYPENEW = "AP" then flag2 = "AP";

else if DECISIONTYPENEW = "DE" then flag2 = "DE";

run;

Accepted Solutions

Solution

06-11-2015
11:14 AM

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

06-11-2015 11:14 AM

You have some of the right pieces in place, but there's still some work to do. Here is one approach:

proc sort data=have;

by T_CLASS_NUM time_variable;

run;

data want;

length flag2 $ 2;

do until (last.T_CLASS_NUM);

set have;

by T_CLASS_NUM;

if DECISIONTYPENEW='AP' then flag2='AP';

else if DECISIONTYPENEW='DE' and flag2=' ' then flag2='DE';

end;

if flag2=' ' then flag2=DECISIONTYPENEW;

do until (last.T_CLASS_NUM);

set have;

by T_CLASS_NUM;

output;

end;

run;

The program goes through all records for a T_CLASS_NUM, to determine the value for FLAG2. Then it goes through the same set of records over again, outputting them with the final FLAG2 value.

Good luck.

All Replies

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

06-11-2015 11:11 AM

Suggest/encourage self-initiated desk-checking -- using SAS debugging techniques below:

- PUTLOG _ALL_; statement in various locations of your DATA step.

- PROC FREQ to analyze value combinations after the DATA step.

- PROC CONTENTS to analyze SAS file variable attributes after DATA step.

Personal coding technique for setting initial data-value (such as flag2) is to assign a blank-constant, however a missing-value (period character) will achieve same as long as the SAS variable is declared (either explicitly or implicitly) as it is expected -- considering some SAS system-default assignments for SAS variable types and lengths (which you would notice with PROC CONTENTS).

Scott Barry

SBBWorks, Inc.

Solution

06-11-2015
11:14 AM

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

06-11-2015 11:14 AM

You have some of the right pieces in place, but there's still some work to do. Here is one approach:

proc sort data=have;

by T_CLASS_NUM time_variable;

run;

data want;

length flag2 $ 2;

do until (last.T_CLASS_NUM);

set have;

by T_CLASS_NUM;

if DECISIONTYPENEW='AP' then flag2='AP';

else if DECISIONTYPENEW='DE' and flag2=' ' then flag2='DE';

end;

if flag2=' ' then flag2=DECISIONTYPENEW;

do until (last.T_CLASS_NUM);

set have;

by T_CLASS_NUM;

output;

end;

run;

The program goes through all records for a T_CLASS_NUM, to determine the value for FLAG2. Then it goes through the same set of records over again, outputting them with the final FLAG2 value.

Good luck.