Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions



Reply
 
Thread Tools Search this Thread Display Modes
  #111  
Old 05-31-2012, 05:35 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,308
Default Dynamic variable name?

Say I have 4 columns of data

Where Rank is always a 1 or 2 and BI1, BI2, and BI3 are never equal to each other.

I want to create a new field and call it "avg" but this will be conditional. If the numerical value in BI3 is equal to zero, then avg=(BI1+BI2)/2, otherwise, and here is where I need help, avg=(BI3+BI(rank))/2

Which is to say, if BI3 equals say 2, then I need to average BI3 with either BI1, or BI2. The indicator for which field I need to average with comes from the field rank.

Is it possible to do this in SAS? I found a work around, but I would like to know if this dynamic way ove choosing the variable to interact with is possible.

Cheers!

Gareth Keenan
Reply With Quote
  #112  
Old 06-01-2012, 11:56 AM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 35,205
Default

Quote:
Originally Posted by Gareth Keenan View Post
Say I have 4 columns of data

Where Rank is always a 1 or 2 and BI1, BI2, and BI3 are never equal to each other.

I want to create a new field and call it "avg" but this will be conditional. If the numerical value in BI3 is equal to zero, then avg=(BI1+BI2)/2, otherwise, and here is where I need help, avg=(BI3+BI(rank))/2

Which is to say, if BI3 equals say 2, then I need to average BI3 with either BI1, or BI2. The indicator for which field I need to average with comes from the field rank.

Is it possible to do this in SAS?
I imagine you could just use a nested if then else statement
or an if then else with a select statement:

IF BI3 = 0 THEN AVG = (BI1+BI2)/2;
ELSE IF RANK = 1 THEN AVG = (BI3+BI1)/2;
ELSE IF RANK = 2 THEN AVG = (BI3+BI2)/2;

"IF RANK = 2" is implied if RANK can only be 1 or 2.

[or (making RANK a character field)]

IF BI3 = 0 THEN AVG = (BI1+BI2)/2;
ELSE SELECT (RANK);
WHEN ('1') AVG = (BI3+BI1)/2;
WHEN ('2') AVG = (BI3+BI2)/2;
OTHERWISE;
END;

[or (if RANK has to be a numeric field)]

IF BI3 = 0 THEN AVG = (BI1+BI2)/2;
ELSE SELECT;
WHEN (RANK = 1) AVG = (BI3+BI1)/2;
WHEN (RANK = 2) AVG = (BI3+BI2)/2;
OTHERWISE;
END;

If I understood your problem correctly I think one of those should be sufficient.

I don't like to leave a naked OTHERWISE statement, so I might change that line to something like
OTHERWISE AVG = 0;
or put in some other error handling.

RUN; SAS; RUN;
RUN; RUN; RUN;

Last edited by 1695814; 06-01-2012 at 01:17 PM..
Reply With Quote
  #113  
Old 06-01-2012, 05:01 PM
McBride McBride is offline
Member
CAS SOA
 
Join Date: Oct 2006
Posts: 73
Default

Challenging question! This one took some research. Turns out the answer is pretty straight forward and I learned something helpful!

You do it using an array.

Code:

DATA TempLib.Temp;
infile datalines delimiter=',';
input B1 B2 B3 Rank;
datalines;
3, 1, 2, 1
2, 5, 0, 2
5, 8, 9, 1
9, 7, 6, 2
9, 7, 0, 2
;
run;
Data TempLib.Temp_02;
SET TempLib.Temp;
Array AA[3] B1-B3;
 
If AA{3} = 0 Then Avrg = ((AA{1} + AA{2})/2);
Else Avrg = ((AA{3} + AA{Rank})/2);
RUN;
 
 
So the first data step was just for me to create a dataset to work with.

The second data step contains the array.

Array AA[3] names the array and defines it's size. B1 - B3 fills the 3 array positions with the values from the B1, B2 and B3 fields. (I didn't know you could do this with arrays. I'm used to creating arrays and filling them using formulas and such.)

Now, you can refer to the array elements using their index number, so AA{1} is array element 1, AA{2} is array element 2 and so on. This lets you use the values from the Rank field as an index to pick up the correct column.

The nice thing about this is it is very easy to expand to more than 3 columns if necessary.

Hopefully this makes sense.

Quote:
Originally Posted by Gareth Keenan View Post
Say I have 4 columns of data

Where Rank is always a 1 or 2 and BI1, BI2, and BI3 are never equal to each other.

I want to create a new field and call it "avg" but this will be conditional. If the numerical value in BI3 is equal to zero, then avg=(BI1+BI2)/2, otherwise, and here is where I need help, avg=(BI3+BI(rank))/2

Which is to say, if BI3 equals say 2, then I need to average BI3 with either BI1, or BI2. The indicator for which field I need to average with comes from the field rank.

Is it possible to do this in SAS? I found a work around, but I would like to know if this dynamic way ove choosing the variable to interact with is possible.

Cheers!

Gareth Keenan
Reply With Quote
  #114  
Old 06-04-2012, 04:19 PM
crabber crabber is offline
Member
CAS
 
Join Date: Nov 2008
Posts: 10,371
Default

Quote:
Originally Posted by McBride View Post
He's right, SAS/Access is what you need for PC files and it will not work without Base SAS.
Any idea which product/products I should be interested in? I spoke with a SAS representative who suggested SAS Office Analytics based on what I told her about my company and background.
Reply With Quote
  #115  
Old 06-05-2012, 11:40 AM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Quote:
Originally Posted by crabber View Post
Any idea which product/products I should be interested in? I spoke with a SAS representative who suggested SAS Office Analytics based on what I told her about my company and background.
If your goal is to learn SAS, start with just Base SAS (why pay them extra $ for products you're not going to use until you have the time to learn the basics and then get around to the more advanced products?). Once you get the basics from Base SAS down, you can go from there- SAS Stat is likely the next product you'll want to dive into to perform GLMs and the like. Base SAS comes with the ability to import files from Excel and MS Access.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #116  
Old 06-05-2012, 12:28 PM
crabber crabber is offline
Member
CAS
 
Join Date: Nov 2008
Posts: 10,371
Default

Quote:
Originally Posted by BassFreq View Post
If your goal is to learn SAS, start with just Base SAS (why pay them extra $ for products you're not going to use until you have the time to learn the basics and then get around to the more advanced products?). Once you get the basics from Base SAS down, you can go from there- SAS Stat is likely the next product you'll want to dive into to perform GLMs and the like. Base SAS comes with the ability to import files from Excel and MS Access.
My personal goal is to learn SAS, but I would also like to be able to quickly demonstrate its potential usefulness, to the extent that such a thing is possible. We currently use MS Office products, and it takes a long time to prep the data to even do a simple analysis, and even then we rely on one-way analysis. I had hoped to do a couple of simple data tasks side by side, and maybe a "simple" GLM analysis of one or two variables and compare them to the one-way analysis. Maybe this is asking too much.
Reply With Quote
  #117  
Old 06-05-2012, 01:02 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Quote:
Originally Posted by crabber View Post
... it takes a long time to prep the data to even do a simple analysis, and even then we rely on one-way analysis. I had hoped to do a couple of simple data tasks side by side, and maybe a "simple" GLM analysis of one or two variables and compare them to the one-way analysis. Maybe this is asking too much.
I don't think that's asking too much. Base SAS alone will probably be enough to allow you to replicate the data tasks and one-way analysis. SAS Stat would enable you to run GLMs.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #118  
Old 10-26-2012, 01:31 AM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,308
Default Get the sum with proc means or summary?

Is there a way to use proc means or proc summary to get a sum? I could have swore you could do that. I don't remember the exact syntax but it was something like

proc means noprint data=dataset out=newdata sum;
var premium;
class state;
quit;

I feel like I shouldn't have to sum using proc sql.

Thanks!

Gareth Keenan
Reply With Quote
  #119  
Old 10-26-2012, 10:38 AM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,249
Default

Proc means and proc summary are the same except that summary doesn't have a print option, so proc means No Print is the same as proc summary

To answer the question that you asked:
Code:
Proc Summary Data = dataIn;
Class V1;*List of descriptive values (if they're sorted then you can say "BY" rather than Class);
Var SumVar;*List of values to sum;
Output out = dataOut Sum=;
run;
if you don't want the _TYPE_ and _FREQ_ fields, then you replace the output line with:
Code:
Output out = dataOut (DROP= _TYPE_ _FREQ_) Sum=;
you can find other tricks in this paper:
http://www.lexjansen.com/sugi/sugi21/at/045-21.pdf

I particularly like what he says on page 3 case 3 where he describes how to do compute different operations on the same dataset. I always need to look up that syntax as I do it so infrequently.
Reply With Quote
  #120  
Old 12-26-2012, 12:35 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,308
Default Losing data when merging two tables, how can I avoid this?

I've got to data sets i'm trying to join based on several fields, and I want to include everything, which is to say if there is a record in the left table that has no matches in the right table, I want to still include the left table as it is, and have the appropriate empty values for the fields from the right tables(you know, the '.'s)

Further, if there is a record in the right table which has no match in the left table, I would like to inlude these records, and include blanks for the field which have no matches.

Basically, I want to merge two data sets and include everything.

What I thought would have worked fine was either a data merge, or a proc sql full join. But I'm having the problem with my full join. I have also proc sorted by the variables I'll be joining on.

There is a field I'm joining on called for simplicities sake just "code." Now, in my left table I have "code" values of A, B, C, D, and E. But the right table has only "code" values of A, B, and E.

After I run

Proc Sql;
create table thetable as select*
from Ltable as L full join Rtable as R
on L.code=R.code;
quit;

when I look at my new data set thetable, I have "code values" A, B, and E, and "." What I would like, is to have A, B, C, D, E and "." if when necessary.

Any suggestions?

Thanks!

Gareth Keenan
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 09:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.57524 seconds with 10 queries