BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dunga
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16
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

5 REPLIES 5
Reeza
Super User

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
Amethyst | Level 16
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
dunga
Obsidian | Level 7

Hi Reeza, Jega, Kurt and RW9,

 

Many thanks to each one of you!

 

Mirisa

Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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