Horizontal processing of a dataset?

[dunga]

/*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*/

[Reeza]

Create an array and loop backwards through it

array month_vars(6) status_;
do i=dim(month_vars) to 1 by -1;
***code;
end;

[Jagadishkatam · Accepted solution]

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;

[dunga]

Hi Reeza, Jega, Kurt and RW9,

Many thanks to each one of you!

Mirisa

[Kurt_Bremser]

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.

[RW9]

Its pretty much exactly the same (question) logic as given in your other question here:

https://communities.sas.com/t5/Base-SAS-Programming/Data-rule-for-horizontal-records/m-p/293192#M609...

Except change missing to E.