creating a new column

[malakaext]

Hello,

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.

Thanks 

[Eva]

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';

data work.myoutput;

     set libxls.'mysheet$'n; /* get acces to one excel sheet */

     if mod(_n_,30)+1 = 1 then do;

          call symput('b',var2);

     end;

     var3 = var1 * symget('b');

run;

libname libxls clear;

[untested code - can you work with that?]

[malakaext]

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

[Eva]

For mod() see here:

http://support.sas.com/documentation/cdl/en/imlug/59656/HTML/default/viewer.htm#langref_sect173.htm

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?

[malakaext]

Thank you.

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.

Thanks.

[DBailey]

Do you have flexibility in creating the spreadsheet?  It would be much easier if you aligned the entries in column c to the date ranges you need.

[malakaext]

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.

Thanks.

[Reeza]

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.

[malakaext]

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?

Thanks

[Reeza]

Please post some example data with the example output.

[malakaext]

I am really sorry but all I have is the data given in the attached csv file named "help.csv".

Is that what you are looking for?

Thanks

I have attache the data set to this reply.