Data visualization with SAS programming

creating a new column

Reply
Frequent Contributor
Posts: 91

creating a new column

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 

Attachment
Regular Contributor
Regular Contributor
Posts: 156

Re: creating a new column

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?]

Frequent Contributor
Posts: 91

Re: creating a new column

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

Regular Contributor
Regular Contributor
Posts: 156

Re: creating a new column

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?

Frequent Contributor
Posts: 91

Re: creating a new column

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.

Attachment
Super Contributor
Posts: 578

Re: creating a new column

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.

Frequent Contributor
Posts: 91

Re: creating a new column

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.

Super User
Posts: 19,170

Re: creating a new column

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.

Frequent Contributor
Posts: 91

Re: creating a new column

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

Super User
Posts: 19,170

Re: creating a new column

Please post some example data with the example output.

Frequent Contributor
Posts: 91

Re: creating a new column

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.

Attachment
Ask a Question
Discussion stats
  • 10 replies
  • 463 views
  • 8 likes
  • 4 in conversation