DATA Step, Macro, Functions and more

retain with by statement

Accepted Solution Solved
Reply
New User
Posts: 1
Accepted Solution

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: 5,499

Re: retain with by statement

Posted in reply to goldenone

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


All Replies
Super Contributor
Super Contributor
Posts: 3,174

Re: retain with by statement

Posted in reply to goldenone

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: 5,499

Re: retain with by statement

Posted in reply to goldenone

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.

Need further help from the community? Please ask a new question.

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