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.
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!
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.
Ready to level-up your skills? Choose your own adventure.