topic Re: Joining Data on Periods in SAS Programming

Joining Data on Periods

anweinbe — Tue, 19 May 2020 14:22:51 GMT

Good morning,

I have a data set that gives me some forecast information by period for a series of stock tickers. However, each of the records shows the current date only. I want to create a file that shows me the current period and the next period all on the same line. The period dates will not be consistent for each stock unfortunately.

I attached an excel file that has the data that I have and what I'm looking for. Essentially, Just taking the next date for that ticker and pulling it back into the original row. So if the row has a February Date, and the next period is a March Date, I want to show the March Date on the February line too.

Thank you!!!

Re: Joining Data on Periods

PaigeMiller — Tue, 19 May 2020 14:44:59 GMT

Most of us will not download and open a Microsoft Office file, as they are security threats. Please provide (a portion of) your data as SAS data step code, as explained in these instructions:

How to create a data step version of your data AKA generate sample data for forums

Re: Joining Data on Periods

anweinbe — Tue, 19 May 2020 14:56:55 GMT

Noted! Let me see if I can get it to work. Currently its erroring out.

Re: Joining Data on Periods

anweinbe — Tue, 19 May 2020 15:05:22 GMT

Here is the data. My goal is to add an additional column so that the next period also shows on the row. For example, record 5 would show the january date as is, but a new column would show the february date.

AAPL

03783310

20FEB2020

EPS

AAPL

03783310

20FEB2020

EPS

AAPL

03783310

20FEB2020

EPS

AAPL

03783310

20FEB2020

EPS

AAPL

03783310

16JAN2020

EPS

AAPL

03783310

16JAN2020

EPS

AAPL

03783310

16JAN2020

EPS

AAPL

03783310

16JAN2020

EPS

AAPL

03783310

19DEC2019

EPS

AAPL

03783310

19DEC2019

EPS

AAPL

03783310

19DEC2019

EPS

AAPL

03783310

19DEC2019

EPS

Re: Joining Data on Periods

PaigeMiller — Tue, 19 May 2020 15:08:45 GMT

@anweinbe wrote:

Noted! Let me see if I can get it to work. Currently its erroring out.

Here's a different link

https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

which may be more usable, sometimes people cannot download directly from github.

Data provided as screen captures is not usable.

Re: Joining Data on Periods

DavePrinsloo — Tue, 19 May 2020 15:11:00 GMT

could you provide an extra table with the wanted results, with the extra cloumn

Re: Joining Data on Periods

ballardw — Tue, 19 May 2020 15:14:12 GMT

@anweinbe wrote:

Here is the data. My goal is to add an additional column so that the next period also shows on the row. For example, record 5 would show the january date as is, but a new column would show the february date.

AAPL 03783310 20FEB2020 EPS 7

2 AAPL 03783310 20FEB2020 EPS 6

3 AAPL 03783310 20FEB2020 EPS 9

4 AAPL 03783310 20FEB2020 EPS 8

5 AAPL 03783310 16JAN2020 EPS 7

6 AAPL 03783310 16JAN2020 EPS 8

7 AAPL 03783310 16JAN2020 EPS 6

8 AAPL 03783310 16JAN2020 EPS 9

9 AAPL 03783310 19DEC2019 EPS 9

10 AAPL 03783310 19DEC2019 EPS 7

11 AAPL 03783310 19DEC2019 EPS 8

12 AAPL 03783310 19DEC2019 EPS 6

If your "record 5" is showing the "February" date wouldn't that be PREVIOUS not NEXT?

And I suspect you are missing part of the description. What role does the variable that shows values of AAPL and possibly 03783310 play in this problem?

The data step as @PaigeMiller indicated would at least let us know what the variable names are to talk about.

Re: Joining Data on Periods

anweinbe — Tue, 19 May 2020 15:42:18 GMT

DATA2DATASTEP DSN IBES_NEW_APPLE

DATA2DATASTEP FILE homesyranweinbe/create_WORK_IBES_NEW_APPLE_data.sas

DATA2DATASTEP FMT YES

DATA2DATASTEP FMTLIST STATPERS DATE9.

DATA2DATASTEP INPUTLIST TICKER:$6. CUSIP:$8. STATPERS:DATE9. MEASURE:$6. FPI:$1.

DATA2DATASTEP LBL YES

DATA2DATASTEP LBLLIST TICKER="IBES Ticker Symbol" CUSIP="CUSIP/SEDOL" STATPERS="I/B/E/S Statistical Period (SAS Format)" MEASURE="Measure" FPI="Forecast Period Indicator"

DATA2DATASTEP LIB TEMP

DATA2DATASTEP LS 256

DATA2DATASTEP MEMLABEL

DATA2DATASTEP MSGTYPE NOTE

DATA2DATASTEP OBS 20

DATA2DATASTEP OUTLIB WORK

DATA2DATASTEP VARLIST TICKER CUSIP STATPERS MEASURE FPI

data WORK.IBES_NEW_APPLE;

infile datalines dsd truncover;

input TICKER:$6. CUSIP:$8. STATPERS:DATE9. MEASURE:$6. FPI:$1.;

format STATPERS DATE9.;

label TICKER="IBES Ticker Symbol" CUSIP="CUSIP/SEDOL" STATPERS="I/B/E/S Statistical Period (SAS Format)" MEASURE="Measure" FPI="Forecast Period Indicator";

datalines;

AAPL 03783310 20FEB2020 EPS 6

AAPL 03783310 20FEB2020 EPS 7

AAPL 03783310 20FEB2020 EPS 8

AAPL 03783310 20FEB2020 EPS 9

AAPL 03783310 16JAN2020 EPS 6

AAPL 03783310 16JAN2020 EPS 7

AAPL 03783310 16JAN2020 EPS 8

AAPL 03783310 16JAN2020 EPS 9

AAPL 03783310 19DEC2019 EPS 6

AAPL 03783310 19DEC2019 EPS 7

AAPL 03783310 19DEC2019 EPS 8

AAPL 03783310 19DEC2019 EPS 9

AAPL 03783310 14NOV2019 EPS 6

AAPL 03783310 14NOV2019 EPS 7

AAPL 03783310 14NOV2019 EPS 8

AAPL 03783310 14NOV2019 EPS 9

AAPL 03783310 17OCT2019 EPS 6

AAPL 03783310 17OCT2019 EPS 7

AAPL 03783310 17OCT2019 EPS 8

AAPL 03783310 17OCT2019 EPS 9

;;;;

Hopefully this is what you guys need. Sorry my SAS skills are very novice level. 

The AAPL and the numbers are just unique identifiers for "Apple". Ideally I would have more stock symbols in the full file. 

to simplify, I would like the following:
AAPL 03783310 20FEB2020 EPS 6 16JAN2020

Re: Joining Data on Periods

ballardw — Tue, 19 May 2020 17:18:00 GMT

It does not look like "next" either. It appears that perhaps you mean to match on FPI as well as your ticker. Maybe.

Your example output really needs to provide more examples such as what would the result be for

AAPL 03783310 20FEB2020 EPS 7
AAPL 03783310 20FEB2020 EPS 8
AAPL 03783310 20FEB2020 EPS 9
AAPL 03783310 16JAN2020 EPS 6

And if your Measure variable plays any role in this you need to describe that as well.

Post the code in a code box opened on the forum with the </> or "running man" icon.

I am going to guess that perhaps you want something like:

proc sql;
   create table want as
   select a.ticker, a.cusip, a.statpers, a.measure, a.fpi
         ,b.statpers as odate
   from WORK.IBES_NEW_APPLE as a left join WORK.IBES_NEW_APPLE as b
      on a.ticker=b.ticker
      and a.fpi=b.fpi
      and intck('month',b.statpers,a.statpers) =1
   order by a.ticker,  a.statpers descending, a.fpi ;
quit;