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.
@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 FThe data type as below:
Services 20 Character
buy_sell 5 Character
seq_number 8 num
trade_id 10 Character
Type 1 Character
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.