topic Re: using variables from two different tables in ProcSQL in SAS Programming

How to filter weekly price data?

igsteo — Wed, 02 Jun 2021 06:21:41 GMT

data work.monthlystock;
set work.stock;
if date = Intnx('MONTH', date, 0, 'END');
run;

I have managed to use the codes attached to filter 'monthly' price data.

Thank you

Re: How to filter weekly price data?

PeterClemmensen — Wed, 03 Feb 2021 08:48:04 GMT

Show us your data please.

Re: How to filter weekly price data?

ballardw — Wed, 03 Feb 2021 10:56:01 GMT

@igsteo wrote:
data work.monthlystock;
set work.stock;
if date = Intnx('MONTH', date, 0, 'END');
run;
The historical data of the stock is 2007-11-07 to 2021-01-19.

I have managed to use the codes attached to filter 'monthly' price data.

However, how do i filter to get weekly price data?

Thank you

Please describe what specific period, as in days of the week and such that you are looking for.

Re: How to filter weekly price data?

mkeintz — Thu, 04 Feb 2021 03:39:12 GMT

If the last trading day of every week were on a Friday, then you could do something analogous to your code:

data work.monthlystock;
  set work.stock;
  if date = Intnx('WEEK.7', date, 0, 'END');
run;

But not all Fridays are trading days. Instead you might want the last trading day of the week (where "week" is defined as starting on Saturday and ending on Friday, i.e. "week.7" in sas date-interval terms.

So you could look for the last trading day such that the next trading is in the next "week.7" period, as in:

data want;
  set stocks;
  by stockid;
  if eod2=0 then set stocks (firstobs=2 keep=date rename=(date=nxt_date)) end=eod2;
  else nxt_date='31dec2030'd;

  if intck('week.7',date,nxt_date)>0 or last.stockid;
run;

This assume data are sorted by stockid/date.

Re: How to filter weekly price data?

igsteo — Fri, 05 Feb 2021 08:44:55 GMT

data mylib.mnprice;
set mylib.pricedata;
if date = Intnx('MONTH', date, 0, 'END');
run;

The following are my codes and the output. However, i realise months are missing as you can see january 2016, april 2016 is not included.

My start date is 31DEC2015 to 29DEC2020.

How do i include missing months where the date is 28th or 29th?

Thank you

Re: How to filter weekly price data?

Kurt_Bremser — Thu, 04 Feb 2021 08:26:29 GMT

data intermediate;
set have;
week = intnx('week',date,0,'e');
run;

proc sql;
create table want as
  select
    date,
    price
  from intermediate
  group by week
  having date = max(date)
;
quit;

This will always get you the last date that is present for a given week. A week would only miss if there was not even a single price point within that week.

The code is not tested in any way, as no usable data was provided.

Re: How to filter weekly price data?

igsteo — Fri, 05 Feb 2021 08:44:26 GMT

data price;
set pricedata;
if date=intnx('weekday',intnx('month',date,0,'e'),0);
run;

These are my current codes which works except that two months are still missing i.e. 30th july 2020 and 29th December 2020.

How should i modify the codes such that it reflects these two specific data as well?

alternative method i was thinking is could i specify that 31st July 2020, 30th and 31st December 2020 is a holiday. Hence, it will be excluded in the codes above for working days of the week?

If so, what are the codes?

Thank you

Re: How to filter weekly price data?

mkeintz — Fri, 05 Feb 2021 05:31:34 GMT

@igsteo wrote:
data price;
set pricedata;
if date=intnx('weekday',intnx('month',date,0,'e'),0);
run;
These are my current codes which works except that two months are still missing i.e. 30th july 2020 and 29th December 2020.

How should i modify the codes such that it reflects these two specific data as well?

alternative method i was thinking is could i specify that 31st July 2020, 30th and 31st December 2020 is a holiday. Hence, it will be excluded in the codes above for working days of the week?

If so, what are the codes?

Thank you

You could do exactly what I showed you in the case of weekly prices, where Fridays are not always trading days. Compare the date in the current record with the data in the upcoming record - getting the interval between them in months (using the INTCK function). If a month-end boundary is crossed between them, then keep the current record.

My week-oriented code was:

data want;
  set stocks;
  by stockid;
  if eod2=0 then set stocks (firstobs=2 keep=date rename=(date=nxt_date)) end=eod2;
  else nxt_date='31dec2030'd;

  if intck('week.7',date,nxt_date)>0 or last.stockid;
run;

For end of month, just change "week.7" to "month", as in (I'll also change the data set names). I've also dropped the "by stockid" since your data rows are apparently not grouped by stockid:

data price;
  set pricedata;
  if eod2=0 then set pricedata (firstobs=2 keep=date rename=(date=nxt_date)) end=eod2;
  else nxt_date='31dec2030'd;

  if intck('month',date,nxt_date)>0;
run;

The second SET statement starts out at observation 2, so it is always one obs in advance of the current obs from the first SET, giving you a way to look ahead at the next date. The reason it has an "if eod2=0" clause is because a SET without the IF would end the data step one iteration prematurely, since it starts out at obs 2.

Re: using variables from two different tables in ProcSQL

igsteo — Fri, 05 Feb 2021 08:45:16 GMT

proc sql;
create table reits_rp as
select a.*, b.date, b.VAR3, a.*/b.VAR3 as reit_rp
from return as a, bondyield as b
where a.date=b.date;
quit;

a= picture showing stock returns

b= picture showing bond yield

As show above my codes using proc sql i would like to form a new table where every stock returns (a) minus yield (b) and by date. However, the log reflects an error in 'a.*/b.VAR3'

How do i rectify this?

Thank you

Re: using variables from two different tables in ProcSQL

Kurt_Bremser — Fri, 05 Feb 2021 08:23:41 GMT

You want to divide ALL variables in table RETURN (including the date value!) by VAR3 from table BONDYIELD, and store the result in a single column? How should that work?

Such formulas need single variables/columns, you cannot use the asterisk there.

Re: Loop Single Regression

igsteo — Fri, 05 Feb 2021 08:51:59 GMT

Re: Loop Single Regression

Kurt_Bremser — Fri, 05 Feb 2021 08:49:07 GMT

This has nothing to do with the original thread subject, please open a new thread in Statistical Procedures.