SAS and VBA (5) : replace values quickly SAS and VBA both have their unique and quick ways to replace values in one or multiple columns. VBAVBA has a wonderful function Replace for several columns or regions, where the changes are likely to be happened. Sub Replace() With Columns("B") .Replace "F", "Female" .Replace "M", "Male" End WithEnd SubSASUser-defined format by PROC FORMAT is the best way for quick replacements. proc format; value $sex 'F' = 'Female' 'M' = 'Male' ;run;data want; set sashelp.class; format sex $sex.;run;ConclusionFor some data management operations such as string/number replacement, it is better way to use the languages' built-in features, instead of the loops ...
SAS and VBA (4) : fill missing values with last valid observation In many data management routines, it is common to fill the missing values with the last valid one. For example, we want to maintain the patient visit log about several patients, which records their weight for each visit. Given these patients’ absence for the appointments, the data analyst has to fill the the empty weight value with the last valid observation. This log includes three columns: patient ID, visit ID and weight.ptno visit weight1 1 122 1 2 1 3 1 4 123 2 1 156 2 3 3 1 112 3 2 4 1 125 4 2 4 3 ...
SAS and VBA (2) : cross tabulation and bar chart No other tools can challenge Excel’s stance in the data analysis world. I didn’t spot many computers that are not installed with it, and I assume that everybody who faces a computer during work has to use it sometime. With the power of VBA, it is all programmable and could realize very complicated purposes without any mouse-clicking. While it is very popular to compare SAS and R, I feel that it is also meaningful to compare SAS and VBA, since these two are both well supported proprietary softwares from the great companies.Here the example is about cross tabulation and the following visualization ...
SAS and VBA (3) : lower triangle multiplication table Flow control and looping is a very important aspect for any programming language. To see how to index a particular value in the languages’ default data type, creating a lower triangle multiplication table looks like a good test, since it is a simple question but still requires the skills to implement a nested loop and a condition statement.VBA Excel has row number (1, 2, etc.) and column number (A, B, etc.) for each cell. Then in VBA, we can use Range() or Cells() to select those cells in any particular worksheet. So it will be very easy to implement the logic ...
SAS and VBA (1): Conditional formatting “Traffic lighting” applies distinctive colors to any numeric variables to indicate the ranges, which is particularly important for Excel reporting. In SAS, it can be easily realized by a user defined format. For example, if I want to add yellow color attribute to all numeric variables, which are great than 60, I can create a color format and cast it toward the target Excel file created by ODS destination. data class; set sashelp.class;run;* Create a user defined formatproc format; value range 60 - high = 'yellow' other = 'white'; run;* Apply the color format for all numeric variablesods html file ...