DATA Step, Macro, Functions and more

Comparing variables in 2 datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Comparing variables in 2 datasets

[ Edited ]

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.


Accepted Solutions
Solution
‎04-07-2018 10:07 PM
Super User
Posts: 23,980

Re: Comparing variables in 2 datasets

Posted in reply to Bennettr99

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.


 

View solution in original post


All Replies
Solution
‎04-07-2018 10:07 PM
Super User
Posts: 23,980

Re: Comparing variables in 2 datasets

Posted in reply to Bennettr99

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.


 

Super User
Posts: 2,049

Re: Comparing variables in 2 datasets

Posted in reply to Bennettr99
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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 329 views
  • 0 likes
  • 3 in conversation