I would like to take 10 rows at a time from column close_4 and place the average in observation 1 of new column named aveClose_4.
proc sql;
create table STOCKA.testing_ave AS
SELECT close_4, close_3, (high52_4 - low52_4) AS spread4_52wk, (high52_3 - low52_3) AS spread3_52wk,
loop?AVE(close_4 Startobs=2 obs11) AS aveClose_4
/**
repeat averages of 10observations until end of observations
I was hoping to try a loop, within the select statement but got lost
**/
FROM STOCKSA.MERGED_CONTENT ORDER BY 'Ticker/Symbol_4'n ASCENDING;
RUN;
QUIT;
PROC DATASETS NOLIST NODETAILS;
CONTENTS DATA=STOCKSA.testing OUT=STOCKSA.testingcontents;
RUN;
PROC PRINT DATA=STOCKSA.testingcontents; RUN;
PROC PRINT DATA=STOCKSA.testing_ave; RUN;
Thanks in advance.
Average closing price of 10 companies who have alphabetically similar Ticker symbols?
Yes,
I am wishing to do analysis of three letter stocks compared to four letter stocks. I wish to average every 10 observations and compare their values.
I wanted help with the coding to do one column so that I could complete the other columns of similar nature.
Thank you.
Every once in a while, I just feel compelled to say that you should stop and re-think the logic of what you are doing. This is one of those situations.
Averaging 10 consecutive companies closing prices (consecutive in alphabetical ticker symbol) makes no sense. It would be like averaging the telephone numbers of those companies. You can certainly do it, but it seems to be a totally pointless thing to do.
I just need some help in compiling the programming loop or datastep for this process.
Thank you in advance.
If you were given a gift from 10 random stocks according to ticker length groupings ---
given that you had two options;
1. random 10 stocks from three letter tickers
2. random 10 stocks from four letter tickers
That is what I am trying to simulate here.
@TronicLaine wrote:
If you were given a gift from 10 random stocks according to ticker length groupings ---
given that you had two options;
1. random 10 stocks from three letter tickers
2. random 10 stocks from four letter tickers
- Which would on average, give you a higher current value?
- Then, using the spread of the 52 week high - 52 week low, you could assess whether the potential for the stocks to increase in value in the future.
That is what I am trying to simulate here.
Well, if you SORT your tickers, as your example starting data shows, you aren't doing random groupings and that is going to drastically reduce the use of the interpretation of what you claim you are doing. You would be better off, if that is what you actually want to do, is to create random groups (and why size 10??)
You want to compare close_4 for 3-letter vs 4-letter ticker symbols. Why?
And if there is a reason for this, then why exclude 1-letter symbols (F for Ford) and 2-letter (AT for Atlantic Power)?
And why does row 1 have the average of rows 2 to 12 (which is actually 11 values, count it on your fingers),
row 2 average rows 12 to 21? (over laps values from the first as well as being a different count of rows, actually 10 this time). And why isn't row 1 values included in any of the averages?
Or was your implication that you aren't actually attaching those values to the output but want to have a summary?
You could add a row counter using a data step to group rows by 10's but the whole process smells to high heaven for any possible interpretation.
What do you expect to do with those averages after you have them???
If this is only an exercise in some weird programming let us know, we can help with pieces but I really don't want to encourage stupid analysis approaches. I see enough of that on TV news.
I just need some help in compiling the programming loop or datastep for this process.
Thank you in advance.
Here's some example code that hopefully will help. Like others that replied I don't understand the "why?", but that's Ok
Note as you get near the end of the got dataset my code reduces the denominator for the average. For example, when you get to observation #19, you can't average 10 observations as there is only observation #19 & #20. So the code sums them and divides by 2.
data got ;
do i=1 to 20 ;
closingPrice=int(ranuni(0)*10) ;
output ;
end ;
run ;
data want ;
keep i avg ;
if 1=2 then
set got nobs=numberOfObs ;
retain
start 1
end 10
counter 20 ;
if end>numberOfObs then
end=numberOfObs ;
sum=0 ;
do j=start to end ;
set got point=j ;
sum=sum+closingPrice ;
end ;
avg=sum/(end-start+1) ;
output ;
start=start+1 ;
end=end+1 ;
if start>numberOfObs then
stop ;
run ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: