I'm trying to create a new variable called "batch" and then assign the value to it when TR_RT contains "B A T C H" then batch=Account.
The following rows after "B A T C H" will also have the same value as "B A T C H" until the next "B A T C H" is found. Then batch=Account of the new "B A T C H".
I use Base SAS 9.4.
Any help would be greatly appreciated.
Thanks,
Jeff
Have:
Obs | SeqNum | TR_RT | Account |
1 | 5900583193 | B A T C H | 7020 |
2 | 5900583194 | 5010 0005 | 9860608265 |
3 | 5900583195 | 0531 0030 | 9860608265 |
4 | 5900583196 | 0110 0002 | |
5 | 5900583197 | 0631 0027 | |
6 | 5900583198 | 0531 0030 | |
7 | 5900583199 | 0531 0030 | |
8 | 5900583200 | 0531 0030 | |
9 | 5900583201 | B A T C H | 1234 |
10 | 5900583202 | 0531 0030 | |
11 | 5900583203 | 0531 0030 | |
12 | 5900583204 | 0531 0030 | |
13 | 5900583205 | 0531 0030 | |
14 | 5900583206 | 0631 0027 | 9860608265 |
15 | 5900583207 | B A T C H | 4587 |
16 | 5900583208 | 0531 0030 | |
17 | 5900583209 | 0631 0027 | |
18 | 5900583210 | 0531 0030 | |
19 | 5900583211 | 0531 0030 | |
20 | 5900583212 | 0531 0030 | |
21 | 5900583213 | 0531 0030 | |
22 | 5900583214 | 0531 0030 |
Want:
Obs | SeqNum | TR_RT | Account | Batch |
1 | 5900583193 | B A T C H | 7020 | 7020 |
2 | 5900583194 | 5010 0005 | 9860608265 | 7020 |
3 | 5900583195 | 0531 0030 | 9860608265 | 7020 |
4 | 5900583196 | 0110 0002 | 7020 | |
5 | 5900583197 | 0631 0027 | 7020 | |
6 | 5900583198 | 0531 0030 | 7020 | |
7 | 5900583199 | 0531 0030 | 7020 | |
8 | 5900583200 | 0531 0030 | 7020 | |
9 | 5900583201 | B A T C H | 1234 | 1234 |
10 | 5900583202 | 0531 0030 | 1234 | |
11 | 5900583203 | 0531 0030 | 1234 | |
12 | 5900583204 | 0531 0030 | 1234 | |
13 | 5900583205 | 0531 0030 | 1234 | |
14 | 5900583206 | 0631 0027 | 9860608265 | 1234 |
15 | 5900583207 | B A T C H | 4587 | 4587 |
16 | 5900583208 | 0531 0030 | 4587 | |
17 | 5900583209 | 0631 0027 | 4587 | |
18 | 5900583210 | 0531 0030 | 4587 | |
19 | 5900583211 | 0531 0030 | 4587 | |
20 | 5900583212 | 0531 0030 | 4587 | |
21 | 5900583213 | 0531 0030 | 4587 | |
22 | 5900583214 | 0531 0030 | 4587 |
data want;
set have;
retain Batch;
if TR_RT ="B A T C H" then Batch=account;
run;
data want;
set have;
retain Batch;
if TR_RT ="B A T C H" then Batch=account;
run;
Thanks so much Novinosrin.
Result:
Thanks everyone for the help.
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.
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.