Posts

Showing posts with the label Replace

Replace Missing Numeric Values using Missing Option/Proc STDIZE

Image
MISSING OPTION Replacing missing values with the desired value like a zero is always a challenge, especially when we have a dataset with a number of columns to standardize. The OLD WAY of doing it to write a DATA step code with  if……then statements like...   if var=. then var =0; t o make 0 appear instead of . (dot) in the tables output. With the SAS option called missing you can save a lot of typing.  If you place the following SAS option code before the generation of the table output: Option missing=" 0 "; SAS will display 0 (zero) instead of the . (dot) on the table. In fact it can display whatever the character we would like to display for missing values.  You can use a - (line) or * (star). Always change it back the option missing setting to default as  Option Missing='';   otherwise you may endup getting unexpected results; Warning : Since we are using this option to display zeros instead of .(dots), you sh...

How to Import Excel files into SAS

Reading from Excel Spreadsheets: Microsoft Excel spreadsheets can be read from SAS in several ways. Two of these will be demonstrated here. First, PROC IMPORT allows direct access to Excel files through SAS/Access to PC File Formats or access to Comma-Separated (CSV) files through Base SAS. The second method uses the Excel LIBNAME engine. PROC IMPORT The IMPORT procedure reads from external sources and creates a SAS data set. Two sources are Excel spreadsheets and CSV files. A particular SAS/Access product may be required for certain sources, however. In our example, SAS/Access to PC File Formats is required to read an Excel file, but a CSV file can be accessed with Base SAS. General Syntax for PROC IMPORT: PROC IMPORT DATAFILE=" c:\sas\ego.csv " OUT=jeeshim.egov DBMS=CSV REPLACE; For Excel you use the DATAFILE =”filename” option to specify the Excel file to be read. (The TABLE=”tablename” option would be applicable if you were reading from a database such as Microso...