Handout12 - Lecture 12 1. Reading spreadsheets with...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
Lecture 12 1. Reading spreadsheets with problems 2. CSV form of data 3. INFORMATS and FORMATS 4. Structuring programs and project code 5. Review of Proc GLM models 1 Reading spreadsheets with problems Download bad_spreadsheet.xls from the course website. 2
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Import the spreadsheet into SAS as “import_xls” using SAS Import Wizard. Equivalent: Proc IMPORT (see LSB §2.16–2.17). From the SAS log: NOTE: PH6470.BAD_XLS data set was successfully created. Proc PRINT data = PH6470.bad_xls; Proc CONTENTS data = PH6470.bad_xls; 3 v _i _s P s_ i _ r ug t _ 2 e L be _ _ 0 0 d y jc n d _ _ _ D m Oe i d B a m m m o p FFF bc t e M t i i i s h 111 st y r I e n n n e s 123 1 0001065 Mankato F . lost 12 . 105 5 0.2 2 0001066 Minneapolis M 27.8 15 . 112 10 . 3 0001067 Cedar Rapids Iowa male 31.2 7 61 220 0 1.8 4 0001068 Kansas City MO F 29.4 10/24/81 19 143 386 10 . 5. . . . . . 4
Background image of page 2
Data Set Name PH6470.BAD_XLS Observations 5 Member Type DATA Variables 12 #V a r i a b l e T y p eL e nF o r m a tI n f o r m a tL a b e l 3B M I N u m 8 B M I 10 F10 Char 1 $1. $1. F10 11 F11 Char 1 $1. $1. F11 12 F12 Char 1 $1. $1. F12 9 Lymphs Num 8 Lymphs 8P r e d D o s e N u m 8 P r e d D o s e 6 _0_min Num 8 30 min 7 _20_min Num 8 120 min 5 __min Num 8 0 min 2c i t y Char 17 $17. $17. city 1s u b j e c t N u m 8 s u b j e c t 4v i s i t _ d a t e Char 8$ 8 . $ 8 . v i s i t d a t e 5 Fixing the CSV fle When the Data Import Wizard cannot correctly read an Excel spreadsheet, save it as a CSV fle and tell SAS exactly how to read each variable. CSV means Comma Separated Values : each vertical line separating columns is replaced with a comma • Check green marks on cells • Use Find and Select (search and replace) within columns to remove characters From numeric data ( <0.02, 358* ) In Excel save the spreadsheet in CSV Format as bad_spreadsheet_1.csv . 6
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Viewed in Excel, we have lost the leading zeros in the subject IDs: They’re not really gone. Here is bad_spreadsheet_1.csv viewed in a text editor: subject,city, gender,BMI,visit date,0 min,30 min,120 min,PredDose,Lymphs,,, 0001065,Mankato,F,25.6X,lost,12,NA,105,5,0.2,,, 0001066,Minneapolis,M,27.8,,15,,112,10,NA,,, 0001067,Cedar Rapids Iowa,male,31.2,11/13/2002,7,61.00,220,0,1.8,,, 0001068,Kansas City MO,F,29.4,10/24/81,19,143.00,386,10,0.7*,,, ,,,,,,,,,,,, 7 Reading a .CSV fle ( LSB §2.15) Use a data step to read in the CSV fle: Data new; INFILE " path to fle "f i r s t o b s = 2D L M = " , "D S Dm i s s o v e r lrecl=100; INPUT list oF variables, with required IN±ORMATS ; 8
Background image of page 4
These are the options for the INFILE statement: firstobs=2 skip the Frst line with variable names and start reading at line 2 DLM ="," speciFes the delimiter , the thing that separates variables (a comma) DSD treats ,, as a missing value missover If there are more variables to read at the end of the data line, set them to missing instead of continuing on to the next line for them.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/21/2011 for the course PUBH 6470 taught by Professor Williamthomas during the Fall '11 term at University of Florida.

Page1 / 18

Handout12 - Lecture 12 1. Reading spreadsheets with...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online