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

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

#111
05-31-2012, 05:35 PM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308
Dynamic variable name?

Say I have 4 columns of data $\begin{tabular} {l c r} BI1 & BI2 & BI3 & Rank \\ \hline \end{tabular}$

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
#112
06-01-2012, 11:56 AM
 1695814 Member SOA AAA Join Date: Aug 2002 Studying for nothing. I quit. Favorite beer: Root Posts: 35,205

Quote:
 Originally Posted by Gareth Keenan Say I have 4 columns of data $\begin{tabular} {l c r} BI1 & BI2 & BI3 & Rank \\ \hline \end{tabular}$ 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..
#113
06-01-2012, 05:01 PM
 McBride Member CAS SOA Join Date: Oct 2006 Posts: 73

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 Say I have 4 columns of data $\begin{tabular} {l c r} BI1 & BI2 & BI3 & Rank \\ \hline \end{tabular}$ 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
#114
06-04-2012, 04:19 PM
 crabber Member CAS Join Date: Nov 2008 Posts: 10,371

Quote:
 Originally Posted by McBride 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.
#115
06-05-2012, 11:40 AM
 BassFreq Member CAS Join Date: Jun 2003 Location: Chicago Studying for all eternity Favorite beer: Duff Posts: 1,708 Blog Entries: 2

Quote:
 Originally Posted by crabber 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? #116 06-05-2012, 12:28 PM  crabber Member CAS Join Date: Nov 2008 Posts: 10,371 Quote:  Originally Posted by BassFreq 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.
#117
06-05-2012, 01:02 PM
 BassFreq Member CAS Join Date: Jun 2003 Location: Chicago Studying for all eternity Favorite beer: Duff Posts: 1,708 Blog Entries: 2

Quote:
 Originally Posted by crabber ... 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?
#118
10-26-2012, 01:31 AM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308
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;
class state;
quit;

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

Thanks!

Gareth Keenan
#119
10-26-2012, 10:38 AM
 dumples Member CAS Join Date: Sep 2003 Posts: 1,249

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

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.
#120
12-26-2012, 12:35 PM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308
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