topic Re: Horizontal processing of a dataset? in SAS Programming

Horizontal processing of a dataset?

dunga — Mon, 22 Aug 2016 21:15:53 GMT

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

Re: Horizontal processing of a dataset?

Reeza — Mon, 22 Aug 2016 21:30:02 GMT

Create an array and loop backwards through it

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

Re: Horizontal processing of a dataset?

Jagadishkatam — Tue, 23 Aug 2016 01:04:34 GMT

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;

Re: Horizontal processing of a dataset?

Kurt_Bremser — Tue, 23 Aug 2016 07:45:59 GMT

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.

Re: Horizontal processing of a dataset?

RW9 — Tue, 23 Aug 2016 08:23:58 GMT

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#M60952

Except change missing to E.

Re: Horizontal processing of a dataset?

dunga — Tue, 23 Aug 2016 18:02:43 GMT

Hi Reeza, Jega, Kurt and RW9,

Many thanks to each one of you!

Mirisa