Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS/GRAPH and ODS Graphics
- /
- creating a new column

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-24-2013 05:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to malakaext

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to malakaext

01-29-2013 02:28 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-29-2013 10:37 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to malakaext

01-29-2013 10:42 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DBailey

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.

Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to malakaext

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

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?

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to malakaext

01-29-2013 11:22 AM

Please post some example data with the example output.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

01-29-2013 11:33 AM