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

Good day,

 

I currently have a dataset that has transaction information for dispute cases.  Each one of these transactions can have multiple actions take place.  Only a certain type of action will show the transaction id.  I am looking for a way to copy that transaction ID to each action within a case.  Using the sample data below I need it to do the following:

 

Look for each trns_nbr, then for every matching Acnt_nbr, dispute_nbr, and act_seq that is the same it should fill in the trns_nbr.

so below should fill in 9876 in the 2nd and 3rd row and then fill in 5678 on the 5th and 6th row.  The table will have a couple thousands lines to look through.

 

acnt_nbr      dispute_nbr      act_seq      act_name        trns_nbr

12345               abcd                  1              XY                  9876

12345               abcd                  1              YZ 

12345               abcd                  1              CV

12345               abcd                  2              XY                  5678

12345              abcd                   2              YZ

12345              abcd                   2              CV

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Good day, @Sotarkadin,

 

Try this:

data have;
input acnt_nbr dispute_nbr $ act_seq  act_name $ trns_nbr;
cards;
12345 abcd 1 XY 9876
12345 abcd 1 YZ .
12345 abcd 1 CV .
12345 abcd 2 XY 5678
12345 abcd 2 YZ .
12345 abcd 2 CV .
;

data want;
do until(last.act_seq);
  set have;
  by acnt_nbr dispute_nbr act_seq notsorted;
  if ~missing(trns_nbr) then tn=trns_nbr;
end;
do until(last.act_seq);
  set have;
  by acnt_nbr dispute_nbr act_seq notsorted;
  if missing(trns_nbr) then trns_nbr=tn;
  output;
end;
drop tn;
run;

Edit: This assumes that your input dataset is at least grouped by the three BY variables, i.e., groups of identical combinations of their values form contiguous blocks of observations.

View solution in original post

1 REPLY 1
FreelanceReinh
Jade | Level 19

Good day, @Sotarkadin,

 

Try this:

data have;
input acnt_nbr dispute_nbr $ act_seq  act_name $ trns_nbr;
cards;
12345 abcd 1 XY 9876
12345 abcd 1 YZ .
12345 abcd 1 CV .
12345 abcd 2 XY 5678
12345 abcd 2 YZ .
12345 abcd 2 CV .
;

data want;
do until(last.act_seq);
  set have;
  by acnt_nbr dispute_nbr act_seq notsorted;
  if ~missing(trns_nbr) then tn=trns_nbr;
end;
do until(last.act_seq);
  set have;
  by acnt_nbr dispute_nbr act_seq notsorted;
  if missing(trns_nbr) then trns_nbr=tn;
  output;
end;
drop tn;
run;

Edit: This assumes that your input dataset is at least grouped by the three BY variables, i.e., groups of identical combinations of their values form contiguous blocks of observations.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 1 reply
  • 675 views
  • 0 likes
  • 2 in conversation