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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.