
#111




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 
#112




Quote:
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; 06012012 at 12:17 PM.. 
#113




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] B1B3; If AA{3} = 0 Then Avrg = ((AA{1} + AA{2})/2); Else Avrg = ((AA{3} + AA{Rank})/2); RUN; 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:

#115




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




Quote:

#117




Quote:
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet? 
#118




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 
#119




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; Code:
Output out = dataOut (DROP= _TYPE_ _FREQ_) Sum=; http://www.lexjansen.com/sugi/sugi21/at/04521.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




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 
Thread Tools  Search this Thread 
Display Modes  

