BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
goldenone
Fluorite | Level 6

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;


1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

2 REPLIES 2
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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
PROC Star

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1194 views
  • 0 likes
  • 3 in conversation