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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.