Solved
New Contributor
Posts: 3

# retain with by statement

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
Super User
Posts: 6,751

## Re: retain with by statement

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
Super Contributor
Posts: 3,176

## Re: retain with by statement

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
Super User
Posts: 6,751

## Re: retain with by statement

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.

🔒 This topic is solved and locked.