01-24-2013 05:35 PM
The attached excel sheet contains two columns. I need to create a new column such that first 30 entries of the new column is the productS of the first 30 entries of column 1 and the 1ts entry of column 2.
Next 30 entries of the new column are the productS of the next 30 entries of column 1 with the 2nd entry of column 2 and so on..
Can some one help me with this code.
01-25-2013 12:05 PM
I think you could use _n_ in a data step which gives you the row number. And then read the value of column2 for each 31st row. And keep it in mind. So that you can multiplicate each of the 30 rows in column1 with this value. I guess that's what you ask for?
Let's say your columns are called var1 and var2 and you create var3.
/* get access to the excel file */
libname libxls 'c:\temp\mydata.xls';
set libxls.'mysheet$'n; /* get acces to one excel sheet */
if mod(_n_,30)+1 = 1 then do;
var3 = var1 * symget('b');
libname libxls clear;
[untested code - can you work with that?]
01-28-2013 07:11 PM
Thank you very much.
Can you explain the "mod" part a little bit more please.
Also if i need to call entries in a column one by one (inside a loop) how could I change this code?
Thank in advance
01-29-2013 02:28 AM
For mod() see here:
It gives you the rest of a division. In my example it gives you every 31st row.
What do you mean with your second sentence? Do you have an example?
01-29-2013 10:37 AM
Let me explain the real problem I am having.
In the attached sheet I have 3 columns A B C.
Column A has dates.
What I wanted to do was to:
divide the entries in column B corresponding to week 2 of that year by the 1st entry in Column C. (Dates start with week 2)
divide the entries in column B corresponding to week 3 of that year by the 2nd entry in Column C.
divide the entries in column B corresponding to week 4 of that year by the 3rd entry in Column C and so on..
I was trying to use a code similar to the one in my original post but since some weeks don't have 5 entries for each week and the 'year' comes important I guess that method won't work.
I think I need to use a couple of do loops with 'if year=' and 'If week=' commands but I don't have that much experience to write a code for that.
That is the main reason I asked for help from you. I know this part of the code has nothing to do with GRAPHING. However, I have to do a lot plotting once I get this correct and also this community is very active and really helpful. That is why I posted this question in this section.
I really appreciate your input.
01-29-2013 10:54 AM
I actually tried to do the whole thing in excel last night. It was really painful to do it manually as I have so many data points(more than 2000) in column B. That's why I wanted to write a GENERAL SAS CODE so I can do it to any data set.
01-29-2013 11:07 AM
Post what your data looks like and what you WANT to get out of it. I think what you want is relatively simple, but you'll need to join the dataset back to itself with SQL and then do you calculations.
I'd rather make sure before I try anything though.
01-29-2013 11:19 AM
Column A denotes the dates.
Column B denotes stock returns and
Column C has some estimated variances.
I want to divide entries in Column B by entries in column C like I have explained in a previous reply and store the new values in a newly created column.
I think that is what you need to know right?