topic Re: Comparing variables in 2 datasets in SAS Programming

Comparing variables in 2 datasets

Bennettr99 — Sat, 07 Apr 2018 23:07:52 GMT

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

After: (ETF remains the same)

TAQ

SYMBOL NewVar

B 1

C 0

D 0

I've never had to compare variables like this, so any help is appreciated.

Re: Comparing variables in 2 datasets

Reeza — Sat, 07 Apr 2018 23:15:49 GMT

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.

Re: Comparing variables in 2 datasets

novinosrin — Sun, 08 Apr 2018 01:21:32 GMT

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;