SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

function like "looking up"

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

function like "looking up"

Hello Guys

  I have three column called "starring date" "ending date" "open price", now I need create a new column call " close price", that close price should be the "open price " of the "ending date". For example, I want get the close price of "ending date"= 05/17/2015, I need find the "05/17/2015" in column "starring date"=05/17/2015 first, and then let "close price" = "open price" of "starring price " which is "05/17/2015'.

This problem like "looking up" vertical. If you have any ideal please let me know, thank you.  


Accepted Solutions
Solution
‎10-30-2017 01:59 PM
Super User
Posts: 9,932

Re: function like "looking up"

[ Edited ]

Since your dataset is strictly order by both dates, you can immediately do a join back on itself:

data test_m2;
input underlyingsymbol $ underlyingprice expiration :mmddyy10. _datadate :mmddyy10.;
format expiration mmddyy10. _datadate mmddyy10.;
cards;
AAPL 128.95 06/05/2015 05/01/2015
AAPL 127.5201 06/12/2015 05/08/2015
AAPL 128.751 06/19/2015 05/15/2015
AAPL 132.54 06/26/2015 05/22/2015
AAPL 128.65 07/10/2015 06/05/2015
AAPL 127.17 07/17/2015 06/12/2015
AAPL 126.6 07/24/2015 06/19/2015
AAPL 126.792 07/31/2015 06/26/2015
AAPL 123.3 08/14/2015 07/10/2015
AAPL 129.57 08/21/2015 07/17/2015
AAPL 124.48 08/28/2015 07/24/2015
AAPL 121.46 09/04/2015 07/31/2015
AAPL 115.52 09/11/2015 08/07/2015
AAPL 116 09/18/2015 08/14/2015
AAPL 106.0501 09/25/2015 08/21/2015
AAPL 113.29 10/02/2015 08/28/2015
AAPL 109.27 10/09/2015 09/04/2015
AAPL 114.017 10/16/2015 09/11/2015
AAPL 113.45 10/23/2015 09/18/2015
AAPL 114.72 10/30/2015 09/25/2015
AAPL 110.38 11/06/2015 10/02/2015
AAPL 112.09 11/13/2015 10/09/2015
AAPL 111.04 11/20/2015 10/16/2015
AAPL 119.08 11/27/2015 10/23/2015
AAPL 119.5 12/04/2015 10/30/2015
AAPL 121.06 12/11/2015 11/06/2015
AAPL 112.34 12/18/2015 11/13/2015
AAPL 119.03 01/08/2016 12/04/2015
AAPL 113.18 01/15/2016 12/11/2015
AAPL 105.9 01/22/2016 12/18/2015
AAPL 96.96 02/12/2016 01/08/2016
AAPL 97.05 02/19/2016 01/15/2016
AAPL 101.42 02/26/2016 01/22/2016
AAPL 97.085 03/04/2016 01/29/2016
AAPL 94.02 03/11/2016 02/05/2016
AAPL 93.99 03/18/2016 02/12/2016
AAPL 96.91 04/01/2016 02/26/2016
AAPL 103.01 04/08/2016 03/04/2016
AAPL 102.23 04/15/2016 03/11/2016
AAPL 105.92 04/22/2016 03/18/2016
AAPL 109.99 05/06/2016 04/01/2016
AAPL 108.66 05/13/2016 04/08/2016
AAPL 109.85 05/20/2016 04/15/2016
AAPL 105.68 05/27/2016 04/22/2016
AAPL 93.75 06/03/2016 04/29/2016
AAPL 92.72 06/10/2016 05/06/2016
AAPL 90.52 06/17/2016 05/13/2016
AAPL 95.22 06/24/2016 05/20/2016
AAPL 100.35 07/01/2016 05/27/2016
AAPL 97.92 07/08/2016 06/03/2016
AAPL 98.83 07/15/2016 06/10/2016
AAPL 95.33 07/22/2016 06/17/2016
AAPL 93.4 07/29/2016 06/24/2016
AAPL 95.89 08/05/2016 07/01/2016
AAPL 96.68 08/12/2016 07/08/2016
AAPL 98.78 08/19/2016 07/15/2016
AAPL 98.66 08/26/2016 07/22/2016
AAPL 104.19 09/02/2016 07/29/2016
AAPL 107.48 09/09/2016 08/05/2016
AAPL 108.18 09/16/2016 08/12/2016
AAPL 109.36 09/23/2016 08/19/2016
AAPL 106.94 09/30/2016 08/26/2016
AAPL 107.73 10/07/2016 09/02/2016
AAPL 103.13 10/14/2016 09/09/2016
AAPL 114.92 10/21/2016 09/16/2016
AAPL 112.71 10/28/2016 09/23/2016
AAPL 112.97 11/04/2016 09/30/2016
AAPL 114.06 11/11/2016 10/07/2016
AAPL 117.66 11/18/2016 10/14/2016
AAPL 116.6 11/25/2016 10/21/2016
AAPL 113.69 12/02/2016 10/28/2016
AAPL 108.84 12/09/2016 11/04/2016
AAPL 108.43 12/16/2016 11/11/2016
AAPL 110.04 12/23/2016 11/18/2016
AAPL 111.79 12/30/2016 11/25/2016
AAPL 109.86 01/06/2017 12/02/2016
AAPL 113.855 01/13/2017 12/09/2016
AAPL 115.95 01/20/2017 12/16/2016
AAPL 116.52 01/27/2017 12/23/2016
AAPL 115.85 02/03/2017 12/30/2016
AAPL 117.91 02/10/2017 01/06/2017
AAPL 119.02 02/17/2017 01/13/2017
AAPL 120 02/24/2017 01/20/2017
AAPL 121.719 03/03/2017 01/27/2017
AAPL 129.05 03/10/2017 02/03/2017
AAPL 132.12 03/17/2017 02/10/2017
AAPL 135.69 03/24/2017 02/17/2017
AAPL 136.66 03/31/2017 02/24/2017
AAPL 139.755 04/07/2017 03/03/2017
AAPL 139.91 04/21/2017 03/17/2017
AAPL 140.6608 04/28/2017 03/24/2017
ABBV 64.32 06/05/2015 05/01/2015
ABBV 65.24 06/12/2015 05/08/2015
ABBV 65.99 06/19/2015 05/15/2015
ABBV 65.48 06/26/2015 05/22/2015
ABBV 67.4 07/10/2015 06/05/2015
ABBV 67.05 07/17/2015 06/12/2015
ABBV 69.48 07/24/2015 06/19/2015
ABBV 70.46 07/31/2015 06/26/2015
ABBV 69.23 08/14/2015 07/10/2015
ABBV 69.99 08/21/2015 07/17/2015
ABBV 68.08 08/28/2015 07/24/2015
ABBV 70.01 09/04/2015 07/31/2015
ABBV 68.62 09/11/2015 08/07/2015
ABBV 68.65 09/18/2015 08/14/2015
ABBV 65.9 09/25/2015 08/21/2015
ABBV 63.98 10/02/2015 08/28/2015
ABBV 59.77 10/09/2015 09/04/2015
ABBV 59.35 10/16/2015 09/11/2015
ABBV 61.22 10/23/2015 09/18/2015
ABBV 55.74 10/30/2015 09/25/2015
ABBV 55.82 11/06/2015 10/02/2015
ABBV 55.64 11/13/2015 10/09/2015
ABBV 56.53 11/20/2015 10/16/2015
ABBV 50.34 11/27/2015 10/23/2015
ABBV 59.55 12/04/2015 10/30/2015
ABBV 64.13 12/11/2015 11/06/2015
ABBV 59.86 12/18/2015 11/13/2015
ABBV 57.19 01/08/2016 12/04/2015
ABBV 54.04 01/15/2016 12/11/2015
ABBV 55.74 01/22/2016 12/18/2015
ABBV 55.65 02/12/2016 01/08/2016
ABBV 57.34 02/19/2016 01/15/2016
ABBV 58.83 02/26/2016 01/22/2016
ABBV 54.9 03/04/2016 01/29/2016
ABBV 53.12 03/11/2016 02/05/2016
ABBV 52.58 03/18/2016 02/12/2016
ABBV 56 04/01/2016 02/26/2016
ABBV 56.15 04/08/2016 03/04/2016
ABBV 57.73 04/15/2016 03/11/2016
ABBV 56.57 04/22/2016 03/18/2016
ABBV 57.42 05/06/2016 04/01/2016
ABBV 58.47 05/13/2016 04/08/2016
ABBV 59.51 05/20/2016 04/15/2016
ABBV 61.42 05/27/2016 04/22/2016
ABBV 61 06/03/2016 04/29/2016
ABBV 62.51 06/10/2016 05/06/2016
ABBV 62 06/17/2016 05/13/2016
ABBV 59.69 06/24/2016 05/20/2016
ABBV 62.71 07/01/2016 05/27/2016
ABBV 65 07/08/2016 06/03/2016
ABBV 61 07/15/2016 06/10/2016
ABBV 60.01 07/22/2016 06/17/2016
ABBV 59.86 07/29/2016 06/24/2016
ABBV 62.71 08/05/2016 07/01/2016
ABBV 64.16 08/12/2016 07/08/2016
ABBV 63.32 08/19/2016 07/15/2016
ABBV 63.81 08/26/2016 07/22/2016
ABBV 66.23 09/02/2016 07/29/2016
ABBV 66.54 09/09/2016 08/05/2016
ABBV 67.19 09/16/2016 08/12/2016
ABBV 67.1 09/23/2016 08/19/2016
ABBV 64.61 09/30/2016 08/26/2016
ABBV 64.12 10/07/2016 09/02/2016
ABBV 63.36 10/14/2016 09/09/2016
ABBV 63.26 10/21/2016 09/16/2016
ABBV 64.98 10/28/2016 09/23/2016
ABBV 63.07 11/04/2016 09/30/2016
ABBV 62.93 11/11/2016 10/07/2016
ABBV 60.17 11/18/2016 10/14/2016
ABBV 60.98 11/25/2016 10/21/2016
ABBV 57.6 12/02/2016 10/28/2016
ABBV 56.04 12/09/2016 11/04/2016
ABBV 63.1 12/16/2016 11/11/2016
ABBV 60.52 12/23/2016 11/18/2016
ABBV 60.51 12/30/2016 11/25/2016
ABBV 59.43 01/06/2017 12/02/2016
ABBV 61.54 01/13/2017 12/09/2016
ABBV 62.22 01/20/2017 12/16/2016
ABBV 62.34 01/27/2017 12/23/2016
ABBV 62.62 02/03/2017 12/30/2016
ABBV 63.79 02/10/2017 01/06/2017
ABBV 61.99 02/17/2017 01/13/2017
ABBV 61.15 02/24/2017 01/20/2017
ABBV 60 03/03/2017 01/27/2017
ABBV 60.67 03/10/2017 02/03/2017
ABBV 60.42 03/17/2017 02/10/2017
ABBV 61.77 03/24/2017 02/17/2017
ABBV 62.09 03/31/2017 02/24/2017
ABBV 63.34 04/07/2017 03/03/2017
ABBV 65.69 04/21/2017 03/17/2017
ABBV 65.62 04/28/2017 03/24/2017
;
run;

data want;
merge
  test_m2 (in=a)
  test_m2 (
    in=b
    keep=underlyingsymbol underlyingprice _datadate
    rename=(underlyingprice=p30 _datadate=expiration)
  )
;
by underlyingsymbol expiration;
if a;
run;

Note that the data step for example date was created with this program:

data _null_;
set test_m2 (where=(underlyingsymbol in ('AAPL','ABBV'))) end=done;
file 'test_m2.sas';
if _n_ = 1
then do;
  put 'data test_m2;';
  put 'input underlyingsymbol $ underlyingprice expiration :mmddyy10. _datadate :mmddyy10.;';
  put 'format expiration mmddyy10. _datadate mmddyy10.;';
  put 'cards;';
end;
put underlyingsymbol underlyingprice expiration _datadate;
if done
then do;
  put ';';
  put 'run;';
end;
run;

Data steps are the best means for presenting example data, as the code is pure text and passes all firewalls, and no problems from different environments (codepages, 32/64 bitness, operating systems, encoding) can happen.

 

Edit: fixed missing 'd' character at beginning of second code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Contributor
Posts: 29

Re: function like "looking up"

I mean "starting date"..

 

Super User
Posts: 9,932

Re: function like "looking up"

Do you mean to "copy" the price of the maximum date to all previous observations of a group?

If yes, sort with group and reverse order of dates, retain your new variable, and set at first. of your group variable.

For more help, post example data (in a data step with datalines).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 29

Re: function like "looking up"

Posted in reply to KurtBremser

Thanks for your helping, let me explain the story.

Suppose I buy stock on 01/01/2015, the price on that day is $1, and then I sell that stock on 01/15/2015, the price is $2,  to calculate return, I should use $2-$1.  In my date file,  01/01/2015 is variable "start date", 01/15/2015 is "end date", I have another variable called "open price", which means the stock price on start day (back to my example, price of 01/01/2015 is $1, 01/15/2015 is $2" . In order to calculate return, I'm not only need price on start date, but also price on end date, so I need create a new variable called "close price". As I explained in my example, that close price should equal to the "open price" of the "start day" which this "start day" should equal to matched "end day". 

So what I' m looking for is, given a value in column A, found the same value in column B and then copy corresponding value in column C as new variable. 

If you still not understand, please review this resources

https://communities.sas.com/t5/General-SAS-Programming/Question-vlookup-equivalent-in-SAS/td-p/14670...

 very similar,  but 

data have ;

  infile cards dsd dlm='|' truncover;

  length id reports_to_id $8 name expected $20;

  input id name reports_to_id expected ;

cards;

000006|Smith, John|000007|Phil, Elias

000007|Phil, Elias|000010|Santa, Clause

000008|Turner, Sandra|200000|Andrew, Skype

000010|Santa, Clause|200000|Andrew, Skype

200000|Andrew, Skype|000006|Smith, John

520001|Walker, Walker|000006|Smith, John

run;

 

proc sql ;

  create table want as

    select a.*,b.name as reports_to_name

    from have a left join have b

    on a.reports_to_id = b.id

    order by a.id

  ;

quit;

But I don't know what does these code( with line) did, so I don't how to change the code to solve my problem.

 

Sorry, I don't know how to post example data,,,,,, if still confuse you please let me know. Thank you for your help.

 

  

Super User
Posts: 9,932

Re: function like "looking up"

I see no date variables/values in your example data.

Please post real example data for your question and the intended result.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 29

Re: function like "looking up"

Posted in reply to KurtBremser

Please review the attachments, P30 is the variable need be corrected.

For example,  for first line, p30 should be the underlying price of "_datadate" =06/05/2015. 

logic is , first line, _datadate is 05/01/2015,expiration is 06/05/2015, so "P30 " should be the price on expiration data 06/05/2015. so  we looking up 06/05/2015 in “_datadate”,  the "underlying price" of "_datadate_06/05/2015 is 128.65. So p30 should be 128.65

Attachment
Solution
‎10-30-2017 01:59 PM
Super User
Posts: 9,932

Re: function like "looking up"

[ Edited ]

Since your dataset is strictly order by both dates, you can immediately do a join back on itself:

data test_m2;
input underlyingsymbol $ underlyingprice expiration :mmddyy10. _datadate :mmddyy10.;
format expiration mmddyy10. _datadate mmddyy10.;
cards;
AAPL 128.95 06/05/2015 05/01/2015
AAPL 127.5201 06/12/2015 05/08/2015
AAPL 128.751 06/19/2015 05/15/2015
AAPL 132.54 06/26/2015 05/22/2015
AAPL 128.65 07/10/2015 06/05/2015
AAPL 127.17 07/17/2015 06/12/2015
AAPL 126.6 07/24/2015 06/19/2015
AAPL 126.792 07/31/2015 06/26/2015
AAPL 123.3 08/14/2015 07/10/2015
AAPL 129.57 08/21/2015 07/17/2015
AAPL 124.48 08/28/2015 07/24/2015
AAPL 121.46 09/04/2015 07/31/2015
AAPL 115.52 09/11/2015 08/07/2015
AAPL 116 09/18/2015 08/14/2015
AAPL 106.0501 09/25/2015 08/21/2015
AAPL 113.29 10/02/2015 08/28/2015
AAPL 109.27 10/09/2015 09/04/2015
AAPL 114.017 10/16/2015 09/11/2015
AAPL 113.45 10/23/2015 09/18/2015
AAPL 114.72 10/30/2015 09/25/2015
AAPL 110.38 11/06/2015 10/02/2015
AAPL 112.09 11/13/2015 10/09/2015
AAPL 111.04 11/20/2015 10/16/2015
AAPL 119.08 11/27/2015 10/23/2015
AAPL 119.5 12/04/2015 10/30/2015
AAPL 121.06 12/11/2015 11/06/2015
AAPL 112.34 12/18/2015 11/13/2015
AAPL 119.03 01/08/2016 12/04/2015
AAPL 113.18 01/15/2016 12/11/2015
AAPL 105.9 01/22/2016 12/18/2015
AAPL 96.96 02/12/2016 01/08/2016
AAPL 97.05 02/19/2016 01/15/2016
AAPL 101.42 02/26/2016 01/22/2016
AAPL 97.085 03/04/2016 01/29/2016
AAPL 94.02 03/11/2016 02/05/2016
AAPL 93.99 03/18/2016 02/12/2016
AAPL 96.91 04/01/2016 02/26/2016
AAPL 103.01 04/08/2016 03/04/2016
AAPL 102.23 04/15/2016 03/11/2016
AAPL 105.92 04/22/2016 03/18/2016
AAPL 109.99 05/06/2016 04/01/2016
AAPL 108.66 05/13/2016 04/08/2016
AAPL 109.85 05/20/2016 04/15/2016
AAPL 105.68 05/27/2016 04/22/2016
AAPL 93.75 06/03/2016 04/29/2016
AAPL 92.72 06/10/2016 05/06/2016
AAPL 90.52 06/17/2016 05/13/2016
AAPL 95.22 06/24/2016 05/20/2016
AAPL 100.35 07/01/2016 05/27/2016
AAPL 97.92 07/08/2016 06/03/2016
AAPL 98.83 07/15/2016 06/10/2016
AAPL 95.33 07/22/2016 06/17/2016
AAPL 93.4 07/29/2016 06/24/2016
AAPL 95.89 08/05/2016 07/01/2016
AAPL 96.68 08/12/2016 07/08/2016
AAPL 98.78 08/19/2016 07/15/2016
AAPL 98.66 08/26/2016 07/22/2016
AAPL 104.19 09/02/2016 07/29/2016
AAPL 107.48 09/09/2016 08/05/2016
AAPL 108.18 09/16/2016 08/12/2016
AAPL 109.36 09/23/2016 08/19/2016
AAPL 106.94 09/30/2016 08/26/2016
AAPL 107.73 10/07/2016 09/02/2016
AAPL 103.13 10/14/2016 09/09/2016
AAPL 114.92 10/21/2016 09/16/2016
AAPL 112.71 10/28/2016 09/23/2016
AAPL 112.97 11/04/2016 09/30/2016
AAPL 114.06 11/11/2016 10/07/2016
AAPL 117.66 11/18/2016 10/14/2016
AAPL 116.6 11/25/2016 10/21/2016
AAPL 113.69 12/02/2016 10/28/2016
AAPL 108.84 12/09/2016 11/04/2016
AAPL 108.43 12/16/2016 11/11/2016
AAPL 110.04 12/23/2016 11/18/2016
AAPL 111.79 12/30/2016 11/25/2016
AAPL 109.86 01/06/2017 12/02/2016
AAPL 113.855 01/13/2017 12/09/2016
AAPL 115.95 01/20/2017 12/16/2016
AAPL 116.52 01/27/2017 12/23/2016
AAPL 115.85 02/03/2017 12/30/2016
AAPL 117.91 02/10/2017 01/06/2017
AAPL 119.02 02/17/2017 01/13/2017
AAPL 120 02/24/2017 01/20/2017
AAPL 121.719 03/03/2017 01/27/2017
AAPL 129.05 03/10/2017 02/03/2017
AAPL 132.12 03/17/2017 02/10/2017
AAPL 135.69 03/24/2017 02/17/2017
AAPL 136.66 03/31/2017 02/24/2017
AAPL 139.755 04/07/2017 03/03/2017
AAPL 139.91 04/21/2017 03/17/2017
AAPL 140.6608 04/28/2017 03/24/2017
ABBV 64.32 06/05/2015 05/01/2015
ABBV 65.24 06/12/2015 05/08/2015
ABBV 65.99 06/19/2015 05/15/2015
ABBV 65.48 06/26/2015 05/22/2015
ABBV 67.4 07/10/2015 06/05/2015
ABBV 67.05 07/17/2015 06/12/2015
ABBV 69.48 07/24/2015 06/19/2015
ABBV 70.46 07/31/2015 06/26/2015
ABBV 69.23 08/14/2015 07/10/2015
ABBV 69.99 08/21/2015 07/17/2015
ABBV 68.08 08/28/2015 07/24/2015
ABBV 70.01 09/04/2015 07/31/2015
ABBV 68.62 09/11/2015 08/07/2015
ABBV 68.65 09/18/2015 08/14/2015
ABBV 65.9 09/25/2015 08/21/2015
ABBV 63.98 10/02/2015 08/28/2015
ABBV 59.77 10/09/2015 09/04/2015
ABBV 59.35 10/16/2015 09/11/2015
ABBV 61.22 10/23/2015 09/18/2015
ABBV 55.74 10/30/2015 09/25/2015
ABBV 55.82 11/06/2015 10/02/2015
ABBV 55.64 11/13/2015 10/09/2015
ABBV 56.53 11/20/2015 10/16/2015
ABBV 50.34 11/27/2015 10/23/2015
ABBV 59.55 12/04/2015 10/30/2015
ABBV 64.13 12/11/2015 11/06/2015
ABBV 59.86 12/18/2015 11/13/2015
ABBV 57.19 01/08/2016 12/04/2015
ABBV 54.04 01/15/2016 12/11/2015
ABBV 55.74 01/22/2016 12/18/2015
ABBV 55.65 02/12/2016 01/08/2016
ABBV 57.34 02/19/2016 01/15/2016
ABBV 58.83 02/26/2016 01/22/2016
ABBV 54.9 03/04/2016 01/29/2016
ABBV 53.12 03/11/2016 02/05/2016
ABBV 52.58 03/18/2016 02/12/2016
ABBV 56 04/01/2016 02/26/2016
ABBV 56.15 04/08/2016 03/04/2016
ABBV 57.73 04/15/2016 03/11/2016
ABBV 56.57 04/22/2016 03/18/2016
ABBV 57.42 05/06/2016 04/01/2016
ABBV 58.47 05/13/2016 04/08/2016
ABBV 59.51 05/20/2016 04/15/2016
ABBV 61.42 05/27/2016 04/22/2016
ABBV 61 06/03/2016 04/29/2016
ABBV 62.51 06/10/2016 05/06/2016
ABBV 62 06/17/2016 05/13/2016
ABBV 59.69 06/24/2016 05/20/2016
ABBV 62.71 07/01/2016 05/27/2016
ABBV 65 07/08/2016 06/03/2016
ABBV 61 07/15/2016 06/10/2016
ABBV 60.01 07/22/2016 06/17/2016
ABBV 59.86 07/29/2016 06/24/2016
ABBV 62.71 08/05/2016 07/01/2016
ABBV 64.16 08/12/2016 07/08/2016
ABBV 63.32 08/19/2016 07/15/2016
ABBV 63.81 08/26/2016 07/22/2016
ABBV 66.23 09/02/2016 07/29/2016
ABBV 66.54 09/09/2016 08/05/2016
ABBV 67.19 09/16/2016 08/12/2016
ABBV 67.1 09/23/2016 08/19/2016
ABBV 64.61 09/30/2016 08/26/2016
ABBV 64.12 10/07/2016 09/02/2016
ABBV 63.36 10/14/2016 09/09/2016
ABBV 63.26 10/21/2016 09/16/2016
ABBV 64.98 10/28/2016 09/23/2016
ABBV 63.07 11/04/2016 09/30/2016
ABBV 62.93 11/11/2016 10/07/2016
ABBV 60.17 11/18/2016 10/14/2016
ABBV 60.98 11/25/2016 10/21/2016
ABBV 57.6 12/02/2016 10/28/2016
ABBV 56.04 12/09/2016 11/04/2016
ABBV 63.1 12/16/2016 11/11/2016
ABBV 60.52 12/23/2016 11/18/2016
ABBV 60.51 12/30/2016 11/25/2016
ABBV 59.43 01/06/2017 12/02/2016
ABBV 61.54 01/13/2017 12/09/2016
ABBV 62.22 01/20/2017 12/16/2016
ABBV 62.34 01/27/2017 12/23/2016
ABBV 62.62 02/03/2017 12/30/2016
ABBV 63.79 02/10/2017 01/06/2017
ABBV 61.99 02/17/2017 01/13/2017
ABBV 61.15 02/24/2017 01/20/2017
ABBV 60 03/03/2017 01/27/2017
ABBV 60.67 03/10/2017 02/03/2017
ABBV 60.42 03/17/2017 02/10/2017
ABBV 61.77 03/24/2017 02/17/2017
ABBV 62.09 03/31/2017 02/24/2017
ABBV 63.34 04/07/2017 03/03/2017
ABBV 65.69 04/21/2017 03/17/2017
ABBV 65.62 04/28/2017 03/24/2017
;
run;

data want;
merge
  test_m2 (in=a)
  test_m2 (
    in=b
    keep=underlyingsymbol underlyingprice _datadate
    rename=(underlyingprice=p30 _datadate=expiration)
  )
;
by underlyingsymbol expiration;
if a;
run;

Note that the data step for example date was created with this program:

data _null_;
set test_m2 (where=(underlyingsymbol in ('AAPL','ABBV'))) end=done;
file 'test_m2.sas';
if _n_ = 1
then do;
  put 'data test_m2;';
  put 'input underlyingsymbol $ underlyingprice expiration :mmddyy10. _datadate :mmddyy10.;';
  put 'format expiration mmddyy10. _datadate mmddyy10.;';
  put 'cards;';
end;
put underlyingsymbol underlyingprice expiration _datadate;
if done
then do;
  put ';';
  put 'run;';
end;
run;

Data steps are the best means for presenting example data, as the code is pure text and passes all firewalls, and no problems from different environments (codepages, 32/64 bitness, operating systems, encoding) can happen.

 

Edit: fixed missing 'd' character at beginning of second code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 29

Re: function like "looking up"

Posted in reply to KurtBremser

Thank you very much!!!!!!

Contributor
Posts: 29

Re: function like "looking up"

Posted in reply to KurtBremser

here is the sample excel file. the column "close price" is the result I want get.

Contributor
Posts: 29

Re: function like "looking up"

 
Attachment
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 182 views
  • 0 likes
  • 2 in conversation