topic Re: Output next,or next and next,or next and next and next,hahaha in SAS Programming

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

summy — Mon, 09 Dec 2013 13:48:52 GMT

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!

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

data_null__ — Mon, 09 Dec 2013 16:01:08 GMT

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; 

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

Keith — Mon, 09 Dec 2013 17:18:53 GMT

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;

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

summy — Tue, 10 Dec 2013 15:09:21 GMT

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