Uni-Logo
Sie sind hier: Startseite Statistical Consulting Excel files
Artikelaktionen

Excel files

excel2SAS

Statisticians often spend hours transferring Excel tables to SAS files. This time can be better used for evaluation. Hence, please consider:

General

  • Choose a simple table structure that can be easily exported.

  • Colours and evaluations (e.g., mean, median, frequencies) cannot be exported.

  • Make the data anonymous by changing names to numbers.

  • Delete unused cells with the command "delete cells" right clicking the mouse.

Data structure

  • One patient per line, also for multiple measurements.

  • One variable per column.

  • First line: Name the variable according to the SAS convention (see below) in order to make it recognizable to the creator of the Excel file

  • Procedure for several groups: Create a single table with only one column in which the group affiliation is documented (do not create one table per group).

Variable names

  • Use max. 12 characters.

  • Start with a letter; numbers or underscores ( _ ) are possible.

  • Avoid blanks.

  • Name should reflect contents of the variable.

Numbering

  • For multiple measurements: Use names such as MESS1-MESS10.

  • Avoid section headers (e.g., admission, discharge) under which the same variable names are allocated.

  • Do not allocate a variable name twice.

Variables

  • Columns must be uniformly formatted and include uniform entries (i.e., only numbers, only dates (formatted as 03.04.97 or 03.04.1997) or only text).

  • Data privacy protection for dates of birth: Always set a date to the 15th of the month (e.g., 03.04.97 is replaced with 15.04.97).

  • Avoid comments between the values (e.g., cause of death next to date of death). Create instead a separate column (as last column) for comments/characteristics.

  • One value per field only; do not overlay a field with a second value (this is possible in Excel but creates additional observations when transferred to SAS).

  • Avoid special characters (use also only sparingly in text columns and only if unavoidable). In particular, do not use semicolons (as they are interpreted as separators)!

  • Complete all columns (as far as data are present). Do not enter "no" in fields remaining empty, "no" cannot be differentiated from missing values ("data not collected").

  • Mark missing numerical values with a period (.).

  • Delete all columns containing results that were computed from other columns.

  • If calculations have been done in Excel: Input the results as numerical values instead of as formulas, which are recalculated each time the table is opened.

Codes

Create an additional page in the Excel table containing the following information: 1st column: Variable name according to the SAS convention

2nd column: Explanation (label)

3rd column: Codes - Code yes/no questions with 0/1 (1 = yes, 0 = no)

Example 1: weight study

Screenshot1

Screenshot2

Example 2: births

Screenshot3

Screenshot4

 

PDF file of: example tables
PDF file of: excel2sas

Benutzerspezifische Werkzeuge