/*I have this dataset. It has 6 accounts. It shows how the succession of the status of each acct has happened over
a period of 6 months*/
data have;
input acct_num status_Jan $4-5 status_feb $6-7 status_Mar $8-9 status_apr $10-11 status_May $12-13 status_Jun $14-15;
cards;
111 7 . 1 5 E 1
222 7 7 7 7 1 .
333 7 E 1 E 1 1
444 7 7 1 . . .
555 7 . . . . 1
666 7 . . . . .
;
run;
/*Q: I wanted to create a new variable which shows what is the month each acct last ever hit "E" status.
For example, acct 111 has last hit "E" status in May. Acct 333 has last hit "E" status in April.
So, the new variable called "last_hit_E" should return May for acct 111 and should return April for acct 333
*/
/*
Answer table should be like below.
acct_num status_Jan status_feb status_Mar status_apr status_May status_Jun last_hit_E
111 7 . 1 5 E 1 May
222 7 7 7 7 1 . .
333 7 E 1 E 1 1 April
444 7 7 1 . . . .
555 7 . . . . 1 .
666 7 . . . . . .
*/
/*Could someone help me to create the "last_hit_E" variable.
Thanks
Mirisa*/
data want;
set have;
array month(*) status_:;
do i = 1 to dim(month);
if month(i)='E' then new=scan(vname(month(i)),2,'_');
end;
run;
Create an array and loop backwards through it
array month_vars(6) status_;
do i=dim(month_vars) to 1 by -1;
***code;
end;
data want;
set have;
array month(*) status_:;
do i = 1 to dim(month);
if month(i)='E' then new=scan(vname(month(i)),2,'_');
end;
run;
Hi Reeza, Jega, Kurt and RW9,
Many thanks to each one of you!
Mirisa
Actually, you'd be better of with a long instead of a wide data format; a wide format "hides" data in the structure (variable names), making coding unnecessarily difficult.
Consider a dataset that has only acct_num, month and status:
proc sort
data=have (where=(status='E'))
out=temp (drop=status)
;
by acct_num descending month;
run;
data want (rename=(month=last_hit_e));
set temp;
by acct_num;
if first.acct_num;
run;
It's understood that "month" should be a proper SAS date variable, of course.
Its pretty much exactly the same (question) logic as given in your other question here:
Except change missing to E.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: