I am using SAS 9.4
I have two data sets ETF and TAQ.
In ETF, there is a variable called Ticker, and a variable called Overlap
In TAQ, there is a variable called SYMBOL.
The Overlap variable can be a time range, "none", or just blank.
What I need to do is determine if any of the variables in SYMBOL are the same as the ones in TICKER, and if they are the same determine if overlap is a time range. If overlap is a time range then I need to create a new variable and put a 1 indicating there is overlap. Otherwise there would be a 0.
I know I did not explain this clearly, so here is an example:
Before:
ETF TAQ
Ticker Overlap SYMBOL
A 9:30-1:00 B
B 10:30-2:00 B
C None C
D C
D
After: (ETF remains the same)
TAQ
SYMBOL NewVar
B 1
B 1
C 0
C 0
D 0
I've never had to compare variables like this, so any help is appreciated.
1. Use a join (left join with TAQ joining ETF) to get the records from ETF added
2. Use a CASE statement to determine the value of Overlap and what it should be assigned.
SQL is likely the easiest approach IMO and it allows you to do this in one step. The same thing could be done in a data step, just make sure to sort the data sets first.
Data step solution steps
1. Sort by Symbol/Ticker
2. Rename so the variables joining on are the same
3. Merge in a data set
4. Use IF/ELSE IF or a SELECT statement to create the flag
If you need further help, please post the code you have so far and explain how it is not working as expected.
@Bennettr99 wrote:
I am using SAS 9.4
I have two data sets ETF and TAQ.
In ETF, there is a variable called Ticker, and a variable called Overlap
In TAQ, there is a variable called SYMBOL.
The Overlap variable can be a time range, "none", or just blank.
What I need to do is determine if any of the variables in SYMBOL are the same as the ones in TICKER, and if they are the same determine if overlap is a time range. If overlap is a time range then I need to create a new variable and put a 1 indicating there is overlap. Otherwise there would be a 0.
I know I did not explain this clearly, so here is an example:
Before:
ETF TAQ
Ticker Overlap SYMBOL
A 9:30-1:00 B
B 10:30-2:00 B
C None C
D C
D
After: (ETF remains the same)
TAQ
SYMBOL NewVar
B 1
B 1
C 0
C 0
D 0
I've never had to compare variables like this, so any help is appreciated.
1. Use a join (left join with TAQ joining ETF) to get the records from ETF added
2. Use a CASE statement to determine the value of Overlap and what it should be assigned.
SQL is likely the easiest approach IMO and it allows you to do this in one step. The same thing could be done in a data step, just make sure to sort the data sets first.
Data step solution steps
1. Sort by Symbol/Ticker
2. Rename so the variables joining on are the same
3. Merge in a data set
4. Use IF/ELSE IF or a SELECT statement to create the flag
If you need further help, please post the code you have so far and explain how it is not working as expected.
@Bennettr99 wrote:
I am using SAS 9.4
I have two data sets ETF and TAQ.
In ETF, there is a variable called Ticker, and a variable called Overlap
In TAQ, there is a variable called SYMBOL.
The Overlap variable can be a time range, "none", or just blank.
What I need to do is determine if any of the variables in SYMBOL are the same as the ones in TICKER, and if they are the same determine if overlap is a time range. If overlap is a time range then I need to create a new variable and put a 1 indicating there is overlap. Otherwise there would be a 0.
I know I did not explain this clearly, so here is an example:
Before:
ETF TAQ
Ticker Overlap SYMBOL
A 9:30-1:00 B
B 10:30-2:00 B
C None C
D C
D
After: (ETF remains the same)
TAQ
SYMBOL NewVar
B 1
B 1
C 0
C 0
D 0
I've never had to compare variables like this, so any help is appreciated.
data etf;
input ticker $ overlap :$10.;
datalines;
A 9:30-10:30
B 10:30-2:00
C NONE
D .
;
data taq;
input symbol $;
datalines;
C
C
B
B
D
;
proc sql;
create table want as
select a.*,anydigit(overlap)>0 as newvar
from taq a left join etf b
on a.symbol=b.ticker;
quit;
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.
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.