Hi All,
I met with a technical problem.Here is the sample list,and sort by account and month:
accont | month | Rel | Pmt | Remark |
001 | 201211 | R | O | No need output |
001 | 201212 | R | P | Output |
001 | 201301 | R | P | Output |
001 | 201302 | R | O | Output |
001 | 201303 | T | P | No need output |
001 | 201304 | T | O | No need output |
001 | 201305 | R | O | No need output |
001 | 201306 | R | O | No need output |
001 | 201307 | R | P | Output |
001 | 201308 | R | O | Output |
002 | 201302 | R | O | No need output |
002 | 201303 | R | O | No need output |
002 | 201304 | T | P | No need output |
002 | 201305 | R | P | Output |
002 | 201306 | T | P | No need output |
002 | 201307 | R | P | Output |
002 | 201308 | R | O | Output |
002 | 201309 | R | O | Output |
002 | 201310 | R | P | Output |
002 | 201311 | R | P | Output |
003 | 201305 | R | O | No need output |
003 | 201306 | T | O | No need output |
003 | 201307 | R | P | Output |
003 | 201308 | T | P | No need output |
003 | 201309 | R | O | No need output |
The conditons of output the accouunt are:
1.output those Rel="R" and Pmt="P"
2.and output all the subsequent Obs which Rel="R" for the same account.Here subsequent means continuous.
I highlighted which needs to output at the column "Remark"..
I have found a way,and it took so many steps and It's hard to explain to my Boss,
Please help!
Thanks in advance!
using RETAIN to keep the previous value is useful here to make the logic fairly simple.
data have;
input account month Rel $ Pmt $;
datalines;
1 201211 R O
1 201212 R P
1 201301 R P
1 201302 R O
1 201303 T P
1 201304 T O
1 201305 R O
1 201306 R O
1 201307 R P
1 201308 R O
2 201302 R O
2 201303 R O
2 201304 T P
2 201305 R P
2 201306 T P
2 201307 R P
2 201308 R O
2 201309 R O
2 201310 R P
2 201311 R P
3 201305 R O
3 201306 T O
3 201307 R P
3 201308 T P
3 201309 R O
;
run;
data want;
set have;
by account month;
retain flag;
if first.account then flag=0;
flag=(rel='R' and (pmt='P' or flag=1));
if flag=1 then do;
drop flag;
output;
end;
run;
I think that the BY statement option NOTSORTED will be helpful.
using RETAIN to keep the previous value is useful here to make the logic fairly simple.
data have;
input account month Rel $ Pmt $;
datalines;
1 201211 R O
1 201212 R P
1 201301 R P
1 201302 R O
1 201303 T P
1 201304 T O
1 201305 R O
1 201306 R O
1 201307 R P
1 201308 R O
2 201302 R O
2 201303 R O
2 201304 T P
2 201305 R P
2 201306 T P
2 201307 R P
2 201308 R O
2 201309 R O
2 201310 R P
2 201311 R P
3 201305 R O
3 201306 T O
3 201307 R P
3 201308 T P
3 201309 R O
;
run;
data want;
set have;
by account month;
retain flag;
if first.account then flag=0;
flag=(rel='R' and (pmt='P' or flag=1));
if flag=1 then do;
drop flag;
output;
end;
run;
Well,I was shocked,such clear and simple.Thanks Keith!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.