topic Re: function like "looking up" in SAS Data Management

function like "looking up"

Xinhui — Mon, 30 Oct 2017 02:59:46 GMT

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.

Re: function like "looking up"

Xinhui — Mon, 30 Oct 2017 03:13:23 GMT

I mean "starting date"..

Re: function like "looking up"

Kurt_Bremser — Mon, 30 Oct 2017 06:35:25 GMT

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).

Re: function like "looking up"

Xinhui — Mon, 30 Oct 2017 07:44:30 GMT

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/146702

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.

Re: function like "looking up"

Xinhui — Mon, 30 Oct 2017 07:54:19 GMT

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

Re: function like "looking up"

Kurt_Bremser — Mon, 30 Oct 2017 08:09:18 GMT

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

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

Re: function like "looking up"

Xinhui — Mon, 30 Oct 2017 08:37:14 GMT

Re: function like "looking up"

Xinhui — Mon, 30 Oct 2017 08:48:29 GMT

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

Re: function like "looking up"

Kurt_Bremser — Mon, 30 Oct 2017 09:42:56 GMT

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.

Re: function like "looking up"

Xinhui — Mon, 30 Oct 2017 17:59:34 GMT

Thank you very much!!!!!!