retain with by statement

[goldenone]

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;

[sbb]

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.

[Astounding · Accepted solution]

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.