BookmarkSubscribeRSS Feed
malakaext
Calcite | Level 5

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 

10 REPLIES 10
Eva
Quartz | Level 8 Eva
Quartz | Level 8

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
Calcite | Level 5

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
Quartz | Level 8 Eva
Quartz | Level 8

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

Please post some example data with the example output.

malakaext
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1755 views
  • 8 likes
  • 4 in conversation