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;
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.