BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bennettr99
Fluorite | Level 6

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 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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.


 

novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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