DATA Step, Macro, Functions and more

Horizontal processing of a dataset?

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Horizontal processing of a dataset?

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

 


Accepted Solutions
Solution
‎08-23-2016 01:59 PM
Trusted Advisor
Posts: 1,128

Re: Horizontal processing of a dataset?

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;

 

Thanks,
Jag

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Horizontal processing of a dataset?

Create an array and loop backwards through it

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

 

Solution
‎08-23-2016 01:59 PM
Trusted Advisor
Posts: 1,128

Re: Horizontal processing of a dataset?

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;

 

Thanks,
Jag
Contributor
Posts: 31

Re: Horizontal processing of a dataset?

Hi Reeza, Jega, Kurt and RW9,

 

Many thanks to each one of you!

 

Mirisa

Super User
Posts: 6,936

Re: Horizontal processing of a dataset?

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,401

Re: Horizontal processing of a dataset?

[ Edited ]

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 301 views
  • 2 likes
  • 5 in conversation