DATA Step, Macro, Functions and more

Output next,or next and next,or next and next and next,hahaha

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Output next,or next and next,or next and next and next,hahaha

Hi All,

I met with a technical problem.Here is the sample list,and sort by account and month:

accontmonthRelPmtRemark
001201211RONo need output
001201212RPOutput
001201301RPOutput
001201302ROOutput
001201303TPNo need output
001201304TONo need output
001201305RONo need output
001201306RONo need output
001201307RPOutput
001201308ROOutput
002201302RONo need output
002201303RONo need output
002201304TPNo need output
002201305RPOutput
002201306TPNo need output
002201307RPOutput
002201308ROOutput
002201309ROOutput
002201310RPOutput
002201311RPOutput
003201305RONo need output
003201306TONo need output
003201307RPOutput
003201308TPNo need output
003201309RONo 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!


Accepted Solutions
Solution
‎12-09-2013 12:18 PM
Regular Contributor
Posts: 151

Re: Output next,or next and next,or next and next and next,hahaha

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

Re: Output next,or next and next,or next and next and next,hahaha

I think that the BY statement option NOTSORTED will be helpful.

filename FT15F001 temp;
data accont;
   infile FT15F001 DSD DLM='09'X;
  
input accont Month $ (Rel Pmt)(:$1.) Remark &$16.;
  
parmcards;
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
;;;;
   run;
proc print;
  
run;
data accont2;
   set accont;
   by accont rel pmt notsorted;
  
if first.accont then flag=0;
  
retain flag;
   if (rel eq 'R' and pmt eq 'P') then flag=1;
  
if flag then out=1;
  
if last.rel and (rel eq 'R') then flag=0;
  
run;
proc print;
  
by accont;
   id accont;
   run;
Solution
‎12-09-2013 12:18 PM
Regular Contributor
Posts: 151

Re: Output next,or next and next,or next and next and next,hahaha

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;

Contributor
Posts: 25

Re: Output next,or next and next,or next and next and next,hahaha

Well,I was shocked,such clear and simple.Thanks Keith!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 230 views
  • 4 likes
  • 3 in conversation