topic Re: retain with by statement in SAS Programming

retain with by statement

goldenone — Thu, 11 Jun 2015 14:01:08 GMT

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 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;

Re: retain with by statement

sbb — Thu, 11 Jun 2015 15:11:39 GMT

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.

Re: retain with by statement

Astounding — Thu, 11 Jun 2015 15:14:31 GMT

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.