Simple SAS Questions

[Gareth Keenan]

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

[1695814]

Originally Posted by Gareth Keenan

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;

[McBride]

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.

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.

Originally Posted by Gareth Keenan

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

[crabber]

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.

[BassFreq]

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.

[crabber]

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.

[BassFreq]

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.

[Gareth Keenan]

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

[dumples]

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:

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:

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.

[Gareth Keenan]

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