topic How to flag these by looking at the next row of records? in SAS Programming

How to flag these by looking at the next row of records?

imdickson — Sat, 16 Jun 2018 08:51:29 GMT

Good Day everyone here.

I have this scenario on my hand where i was given a Data that requires me to flag into 5 different criteria.

Before i talk about the criteria/rules, let me share the data with you all.

Services	buy_sell	seq_number	trade_id	Type
2009-AAA	Sell		0		100		D
2009-AAA	Buy		0		101		D
2009-BBB	Sell		0		102		D
2009-BBB	Buy		0		103		F
2009-BBB	Buy		0		104		F
2010-CCC	Sell		0		105		F
2010-DDD	Buy		0		106		F
2010-DDD	Buy		0		107		F
2010-EEE	Buy		0		108		D
2010-EEE	Sell		0		109		F
2011-ZZZ        Buy             0               110             F

The data type as below:

Services 20 Character

buy_sell 5 Character

seq_number 8 num

trade_id 10 Character

Type 1 Character

Now here are the Criterias:

1) list out the Services that has Buy_sell Buy and Sell & D and D as type (All this in Pair, could just be 1 pair in most cases but also possible to have more than a pair). From the data above, Services 2009-AAA fulfill this criteria. Flag records in this criteria as 'A'

2) list out Services that has Buy_sell Buy and Sell & F and D as type. (All this in Pair, could just be 1 pair in most cases but also possible to have more than a pair). From the data above, Services 2010-EEE fulfill this criteria. Flag records in this criteria as 'B'

3) List out Services that has Buy_sell Buy and Sell & F and F as type. (All this in Pair, could just be 1 pair in most cases but also possible to have more than a pair). From the data above, Services 2009-BBB fulfill this criteria BUT with additional trade_id of 104. In this case, mark this 3 records(2009-BBB) as Exception flag. Flag records in this criteria as 'C'

4) list out Services that has Buy_sell Buy and Buy or Sell and Sell & any type. (All this in Pair, could just be 1 pair in most cases but also possible to have more than a pair). From the data above, Services 2010-DDD fulfill this criteria. Flag records in this criteria as 'D'

5) List out Services that only has 1 record alone. From the data above, Services 2011-ZZZ fulfill this criteria. Flag records in this criteria as 'E'

This is a big challenge to me as i was thinking to group them first. However, how should i further work from grouped data.

I really hope if anyone can help me with SAS Dataset or Proc Sql or even macro loop if necessary for this case.

A big thanks in advanced.

Re: How to flag these by looking at the next row of records?

Patrick — Sun, 17 Jun 2018 03:15:11 GMT

@imdickson wrote:

Good Day everyone here.

I have this scenario on my hand where i was given a Data that requires me to flag into 5 different criteria.

Before i talk about the criteria/rules, let me share the data with you all.
Services	buy_sell	seq_number	trade_id	Type
2009-AAA	Sell		0		100		D
2009-AAA	Buy		0		101		D
2009-BBB	Sell		0		102		D
2009-BBB	Buy		0		103		F
2009-BBB	Buy		0		104		F
2010-CCC	Sell		0		105		F
2010-DDD	Buy		0		106		F
2010-DDD	Buy		0		107		F
2010-EEE	Buy		0		108		D
2010-EEE	Sell		0		109		F
2011-ZZZ        Buy             0               110             F
The data type as below:

Services 20 Character

buy_sell 5 Character

seq_number 8 num

trade_id 10 Character

Type 1 Character

@imdickson

Please provide a fully tested SAS data step which creates a SAS data set with your sample data. Don't expect us to do this prep work for you.

It's also always appreciated if you demonstrate some of your own work, i.e. posting some not yet working code or some thought how you would approach this.

I'd probably go for an array approach:

First transpose your data to a single observation per Service number, define arrays for the columns and use the WHICHN() WHICHC() functions to find pairs matching your conditions.

What are the volumes you're dealing with? How many rows are approximately in your real source data.

Re: How to flag these by looking at the next row of records?

PGStats — Sun, 17 Jun 2018 20:39:30 GMT

It is not clear if pairs should be consecutive or not. In case they don't need to be consecutive:

 data have;
 input Service $ buy_sell $ seq_number trade_id Type :$1.;
 datalines;
2009-AAA Sell 0 100 D
2009-AAA Buy 0 101 D
2009-BBB Sell 0 102 D
2009-BBB Buy 0 103 F
2009-BBB Buy 0 104 F
2010-CCC Sell 0 105 F
2010-DDD Buy 0 106 F
2010-DDD Buy 0 107 F
2010-EEE Buy 0 108 D
2010-EEE Sell 0 109 F
2011-ZZZ Buy 0 110 F
; 

proc sql;
create table pairs as
select unique 
    a.service,
    case when a.buy_sell<b.buy_sell 
        then catx("-", a.buy_sell, b.buy_sell)
        else catx("-", b.buy_sell, a.buy_sell) end as bs_pair length=8,
    case when a.type<b.type 
        then catx("-", a.type, b.type) 
        else catx("-", b.type, a.type) end as type_pair length=3
from 
    have as a left join
    have as b on a.service=b.service
where a.trade_id > b.trade_id
order by service, bs_pair;
create table services as select unique service from have;
quit;

data want;
merge services pairs(in=inp); by service;

exception = not (first.service and last.service);

if inp then do;
    if bs_pair = "Buy-Sell" and type_pair="D-D" then flag = "A";
    else if bs_pair = "Buy-Sell" and type_pair="D-F" then flag = "B";
    else if bs_pair = "Buy-Sell" and type_pair="F-F" then flag = "C";
    else if bs_pair in ("Buy-Buy", "Sell-Sell") then flag = "D";
    end;

else flag = "E";
run;