topic Re: data with dates in SAS Programming

data with dates

HeatherNewton — Tue, 20 Feb 2024 09:33:00 GMT

if I have a table with financial data lets say regarding index price and I have column date, open , high , low, closing

now I need to do calculation which involve more than one closing price of one date, I might need to use closing price for the past 12 days. What is the best way to represent and refer to these closing prices of different date when writing sas codes?

Re: data with dates

LinusH — Tue, 20 Feb 2024 10:07:57 GMT

If you give a short sample of the data your have, and the data you want, it will be easier to guide you.

Re: data with dates

Patrick — Tue, 20 Feb 2024 11:24:05 GMT

You need definitely to provide some sample data (please as code via a tested SAS data step with datalines/cards that creates the data), show us the desired result based on this sample data and explain the logic you have in mind to get from have to want.

Please be clear if you're after some rolling sums or the like.

Just realized who is asking the question. Soo.... you should know in-between what and how to provide all the info that puts us into a position to fully understand your question and provide an answer.

Re: data with dates

HeatherNewton — Wed, 21 Feb 2024 03:45:35 GMT

ok let me clarify

my data file might look like the following and I need to calculate different indicators e.g. wma, bollinger band, stochastic, rsi etc and I will need to refer to more than prices from more than 1 historical date in the calculation. e.g.

weighted moving average (WMA)

what is the best way to refer to different price from different dates in the file?

Date	Open	High	Low	Close	Adj Close	Volume
2/21/2023	15840.16	15840.16	15551.91	15572.52	15572.52	4121590000
2/22/2023	15572.52	15623.2	15490.18	15530.83	15530.83	4079320000
2/23/2023	15530.83	15654.95	15446.08	15584.93	15584.93	3952940000
2/24/2023	15584.93	15584.93	15347.28	15464.46	15464.46	3877700000
2/27/2023	15464.46	15623.25	15462.8	15492.93	15492.93	3836950000
2/28/2023	15492.93	15518.78	15427.21	15428.97	15428.97	5043400000
3/1/2023	15428.97	15477.92	15378.12	15436.3	15436.3	4249480000
3/2/2023	15436.3	15555.09	15342.71	15524.52	15524.52	4244900000
3/3/2023	15524.52	15732.72	15524.52	15721.06	15721.06	4084730000
3/6/2023	15721.06	15772.65	15672.11	15694.34	15694.34	4000870000
3/7/2023	15694.34	15694.34	15413.54	15439.02	15439.02	3922500000

Re: data with dates

Kurt_Bremser — Wed, 21 Feb 2024 08:28:25 GMT

To calculate over a rolling window, create a temporary array indexed by dates; start and stop values can be calculated like this:

%let start = %sysfunc(inputn(2000-01-01,yymmdd10.));
%let end = %sysfunc(today());

These values are used in the array definition:

array window{&start.:&end.} _temporary_;

Fill the values into the array using the date of the current observation, and use the elements from current date - 11 to current date for your calculation.