Actuarial Outpost Simple SAS Questions
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Salary Surveys Property & Casualty, Life, Health & Pension Health Actuary JobsInsurance & Consulting jobs for Students, Associates & Fellows Actuarial Recruitment Visit DW Simpson's website for more info. www.dwsimpson.com/about Casualty JobsProperty & Casualty jobs for Students, Associates & Fellows

#61
05-24-2011, 05:27 PM
 ADoubleDot Member SOA Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 37,151

Code:
proc sort data = table1;
by pol iteration;
run;

data table2;
set table1;
by pol iteration;
if last.pol then output;
run;
__________________

**** Juan.
#62
05-24-2011, 05:50 PM
 BassFreq Member CAS Join Date: Jun 2003 Location: Chicago Studying for all eternity Favorite beer: Duff Posts: 1,708 Blog Entries: 2

proc sql;
create table table2 as
select *
from table 1
group by pol
having iteration=max(iteration);
quit;

but with 1M+ rows, this will be really inefficient.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
#63
06-14-2011, 03:09 PM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308
Where SAS starts executing a Where in a data step

Say I have a source body of data.

$\begin{matrix} Indicator&Meal&Entree
\\ 1&Dinner&Poultry
\\ 2&Breakfast&Eggs
\\ 3&Lunch&Sandwich
\\ 4&Lunch&Soup
\\ 6&Lunch&Left\ Overs \end{matrix}$

And I have a SAS query, where I am interested in all records that have "Lunch" as the meal, but I'm only interested in Lunches that are at or equal to Indicator 4, is there a way to do this in SAS and minimize CPU cycles or whatever?

Specifically, I want my oracle search to not even worry about lunches, until it gets to an indicator with a value of 4 then to worry about lunches. I also know that my source data is sorted by indicator, so that the values of the indicator are from smallest to largest.

Is there a way to do this? In the very large data set I am looking at so if I could get the query to start only at the precise indicator, the query would take less time to execute.

I have this statement executing in an elementary data step, but if there is a more efficient way to do this, I would love to hear it. Thanks!

Gareth Keenan
#64
06-14-2011, 03:17 PM
 ADoubleDot Member SOA Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 37,151

Try creating indexes on your data. The index tells SAS where the unique values of the data exist on the table. This means that it only needs to search the correct partition.

Code:
proc datasets library = library_name;
modify table_name;
index create indicator;
index create lunch;
run;

data table_name2;
set table_name;
where indicator >= 4 AND meal = 'lunch';
run;
#65
06-18-2011, 07:20 PM
 ZeroUrashima Member Join Date: Apr 2010 Posts: 30

Currently reading Learning SAS by Example: A Programmer's Guide and doing the questions...

I am doing Chapter 21's Question 10 and I wrote the thing that way:
Code:
data SALES INVENTORY;
input @16 Type 1. @;

if Type=1 then do;
input @1 Date mmddyy10.
@12 Amount 4.;
output Sales;
end;

else if Type=2 then do;
input @1 PartNumber $6. @8 Quantity 3.; output Inventory; end; drop type; datalines; 10/21/2005 1001 11/15/2005 2001 A13688 250 2 B11112 300 2 01/03/2005 50001 A88778 19 2 ; run; title "Listing of SALES"; proc print data=Sales; run; title "Listing of INVENTORY"; proc print data=inventory; run; Question asks to create two SAS data sets in one DATA Step and the result they want only have the columns that have non-missing Data. Currently, Sales have a column of PartNumber and Quantity and Inventory has a column of Date and Amount. I suppose I could use var in the procedure to get rid of those but I don't think that's what the question wants since they are still in the Data Set but we just don't see them. Anyone can tell me if there is a way to create 2 Data Sets in one Data Step and the columns with missing values are not there? #66 06-28-2011, 12:04 AM  Olrich Member Join Date: Jan 2008 Posts: 159 I'm sure a lot of people are already aware of them, but I just recently learned about hash tables (as an alternative to sort and merge for joining tables together) and they are making a big difference in the run time of a lot of stuff I use. They aren't perfect for every situation, but anytime you're merging a relatively small table to a very large dataset (pulling mortality rates into an inforce file or whatever), it dramatically cuts runtime. The following is a good introduction, there's some other documentation floating around how to some fancier things with hash tables. http://www2.sas.com/proceedings/forum2007/039-2007.pdf #67 11-15-2011, 11:14 AM  Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308 Sum of a character field? Is there a way in SAS to take the sum of a character field? Lets say I have a field called "field" in a table in my work library called "table". I've visually confirmed by looking up distinct values that for all of my rows in "field" are values that are numbers. Is there a way in Sas I could take the some of "field"? Normally, If I wanted to quickly spot check something, I might just use PROC Means, and review the output, but it looks to me like PROC means does not work on character fields. suggestions? Thanks, Gareth Keenan #68 11-15-2011, 11:22 AM  BassFreq Member CAS Join Date: Jun 2003 Location: Chicago Studying for all eternity Favorite beer: Duff Posts: 1,708 Blog Entries: 2 This would do it... Code:  data table; input field$2.;
datalines;
10
21
68
;
run;
proc sql;select sum(input(field,best32.)) from table;quit;
...but typically I would just convert the character field into numeric using the input funtion and then you could use proc means on that.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
#69
11-15-2011, 11:23 AM
 BassFreq Member CAS Join Date: Jun 2003 Location: Chicago Studying for all eternity Favorite beer: Duff Posts: 1,708 Blog Entries: 2

Actually, not convert the character field, but rather, create a numeric version of it.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
#70
11-29-2011, 03:21 PM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308
if vs else if vs else

Is there a logic for when to use if vs else if vs else ?

I'm creating a new variable in a data step based on another variable I all ready have. I feel like you should end with else, which is like saying, "if none of the above criteria matched, just do whatever the else tells you to do."

I also imagine, you have to begin with an if, otherwise beginning with an else if doesn't make logical sense. It probably doesn't make syntax sense, but if I understood this situation, I would not be posting here for help.

when after your first "if" do you use "if" or "if else" or "else"

Cheers,

Gareth Keenan