DATA Step, Macro, Functions and more

Compare between rows to output all or nothing in the block of observations. Can this be done in SAS?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Compare between rows to output all or nothing in the block of observations. Can this be done in SAS?

Hi everyone,

Here's a sample dataset.

Obs SeqNum Amount NegSign CES_APPL
10400 1700553755 1035 72   FCD
10401 1700553756 53760 90   DDA
10402 1700553757 27746 05   DDA
10403 1700553758 65240 21   AGL
10404 1700553759 5910 97 - ABC
10405 1700553760 91130 71   FCD
10406 1700553761 10888 23   DDA
10407 1700553762 2622 13   DDA
10408 1700553763 124066 24   DDA
10409 1700553764 7067 77   DDA
10410 1700553765 40 21   DDA
10411 1700553766 5831 73   DDA
10412 1700553767 1030 98 - AGL
10413 1700553768 43648 52   DDA
10414 1700553769 18695 12   FCD
10415 1700553770 9439 83   XYZ
10416 1700553771 12219 36   DDA
10417 1700553772 7256 50   DDA
10418 1700553773 40698 54   DDA
10419 1700553774 16211 15 - DDA
10420 1700553775 600 00   DDA
10421 1700553776 53760 90   DDA


I'd like to output some rows which meet the following logic (in this case, obs#10404 through obs#10411) :

(1) The first row to output: If NegSign = (-) and CES_APPL = ABC or AGL  

(2) And then ALL following rows: CES_APPL = DDA or FCD. If it contains something else other than these 2 codes, don't output the whole chunk. 

 

For example:

-Obs#10400 through obs#10403 will NOT be outpout since there is no starting row with NegSign = (-).

-Obs#10404 through obs#10411 will be OUTPUT since obs#10404 NegSign=(-) and CES_APPL = ABC, and then ALL the following rows CES_APPL = DDA or FCD.

-However, obs#10412 through obs#10418 will NOT be output even though obs#10412 NegSign = (-) and CES_APPL = AGL, but one of the following rows (obs#10415) CES_APPL = XYZ. Not ALL CES_APPL = DDA or FCD. It has to be ALL or NOTHING within the block.

-Obs#10419 through obs#10421 will NOT be output since obs#10419 NegSign = (-), but CES_APPL ≠ ABC or AGL.

 

So, my idea is to mark the starting row which meet the criteria (NegSign = (-) and CES_APPL = ABC or AGL.

Then, check criteria (2) for the next row only if (1) is met. Then output, otherwise, don't output.

Stop when the next row NegSign = (-), then go back to check (1) again.

 

I use Base SAS 9.4.

 

I'm not sure this logic could be done in SAS or not.

Any help would be greatly appreciated.

Thanks,

Jeff

 

 


Accepted Solutions
Solution
‎07-17-2017 11:05 AM
Super User
Posts: 5,085

Re: Compare between rows to output all or nothing in the block of observations. Can this be done in

[ Edited ]

It helps to make two passes through the data.  For example:

 

data pass1;

set have;

if NegSign = '-' then block + 1;

run;

 

Then make a second pass, using BLOCK to determine which observations "belong" together:

 

data want;

output_flag = 'Y';

do until (last.block);

   set pass1;

   by block;

   if first.block then do;

      if NegSign = ' ' then output_flag = 'N';

      if CES_APPL not in ('ABC', 'AGL') then output_flag = 'N';

   end;

   else if CES_APPL not in ('DDA', 'FCD') then output_flag = 'N';

end;

do until (last.block);

   set pass1;

   by block;

   if output_flag = 'Y' then output;

end;

drop output_flag block;

run;

View solution in original post


All Replies
Solution
‎07-17-2017 11:05 AM
Super User
Posts: 5,085

Re: Compare between rows to output all or nothing in the block of observations. Can this be done in

[ Edited ]

It helps to make two passes through the data.  For example:

 

data pass1;

set have;

if NegSign = '-' then block + 1;

run;

 

Then make a second pass, using BLOCK to determine which observations "belong" together:

 

data want;

output_flag = 'Y';

do until (last.block);

   set pass1;

   by block;

   if first.block then do;

      if NegSign = ' ' then output_flag = 'N';

      if CES_APPL not in ('ABC', 'AGL') then output_flag = 'N';

   end;

   else if CES_APPL not in ('DDA', 'FCD') then output_flag = 'N';

end;

do until (last.block);

   set pass1;

   by block;

   if output_flag = 'Y' then output;

end;

drop output_flag block;

run;

Occasional Contributor
Posts: 5

Re: Compare between rows to output all or nothing in the block of observations. Can this be done in

[ Edited ]

Thanks so much, Astounding. It works perfectly ^_^

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 91 views
  • 2 likes
  • 2 in conversation