BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Teslator
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

2 REPLIES 2
Astounding
PROC Star

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;

Teslator
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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