Looping through observations and creating a new table

Reply
Senior User
Posts: 1

Looping through observations and creating a new table

Hey!

I am a total beginner in SAS but I have to use it for my bachelor thesis. So my problem is: I have this data set of which you can see a very small extract below (form view). I now want to loop through this table and for every Underlying_Symbol, every date, every Expiration and every strk_prc I want to create a new observation in a new table that contains this information and another variable calculated from the sum over the tradeprices of trades with that specific Underlying, date, ....

It would even help me if someone can show me how I loop over these four variables of my data, especially the date.

 

Thanks in advance!!!

 

 

Super User
Posts: 10,279

Re: Looping through observations and creating a new table

Please post example data in a data step with datalines. A macro for converting exsiting datasets to such a data step can be found here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat....

Advice for posting code is found here: https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce.

Posting Word documents (or Excel files) is not helpful, as many of us won't or can't open them from the web for security reasons.

 

Program text can be easily copy/pasted to our SAS environment and submitted.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,583

Re: Looping through observations and creating a new table

Also along with a small example data set, should be just enough records to show the type of data and use cases you have, an example of the desired output would be helpful.

It may not hurt to describe what will be done with the resultant data set as well. A common issue for many new users of SAS depending on prior experience to attempt to duplicate step-by-step a process used by other software that can be done in SAS with procedures.

 

For instance this phrase: "calculated from the sum over the tradeprices of trades with that specific Underlying, date, ...." (besides being incomplete at the ...) would suggest that you want to sum a variable grouped by one or more other variables.

 

One way would be proc means:

Proc means data=yourdatasetnamegoeshere sum;

    class underlyingdate; /* grouping variable or variables go here*/

    var tradeprice;

run;

This doesn't create an output set but can. But start by seeing if this gets the sums you want. [ Hint: create a small subset of the data and test on that where you could see if the result is close.]

 

Another large economy sized hint is to make sure that your SAS date variables are SAS date valued numeric. This is important as there are a largish number of functions to manipulate dates and SAS can create date-based summaries of different time intervals by just changing a display format.

Super User
Posts: 23,771

Re: Looping through observations and creating a new table

SAS loops automatically so you don't need any loops I suspect. 

It also does BY group processing so you can do similar functions across groups of data. 

 

If you're working with time series, you'll want to look at PROC EXPAND to calculate statistics such as moving averages, means or standard deviation. Make sure to find the examples in the documentation that show how these tasks can be accomplished.

Ask a Question
Discussion stats
  • 3 replies
  • 168 views
  • 0 likes
  • 4 in conversation