topic Re: DATA FORMAT in SAS Procedures

DATA FORMAT

Fred_Gavin — Mon, 20 Apr 2009 16:22:18 GMT

When I import file from EXCEL(XLS), if there exists both missing values and numeric values in a same variable, SAS would read them as a mix variable. So is there anyone know how to transfer this type of format to numeric only? Thanks and Regards.

Re: DATA FORMAT

sbb — Mon, 20 Apr 2009 18:32:19 GMT

SAS variables are either CHARACTER or NUMERIC in type. If you need to convert from one format to another, assign a new SAS variable and use the INPUT or PUT function in a DATA step. Importing with a DATA step provides you more control over how the input data is interpreted, as compared to PROC IMPORT and the SAS-generated program, based on its data interpretation at import time.

Scott Barry
SBBWorks, Inc.

Re: DATA FORMAT

data_null__ — Mon, 20 Apr 2009 19:17:13 GMT

You don't give specific information about IMPORT method. With XL libname engine you can use

DBSASTYPE
Specifies data types to override the default SAS data types during input processing.

Sort of an example.
[pre]
libname x excel 'book1.xls';
data test;
set x.'sheet1$'n(dbsastype=(a='numeric' b='char(2)' c='date'));
run;
proc print;
run;
libname x clear;
[/pre]

Re: DATA FORMAT

Ili — Thu, 23 Apr 2009 12:58:36 GMT

Will the put statement work if you have already imported the file through File>Import? If so, can you give me an example of how to run this? I am having a similar problem when I import an Excel file with alot of missing values, SAS reads the variable as CHAR & i lose the data further down the column.

Re: DATA FORMAT

sbb — Thu, 23 Apr 2009 15:17:43 GMT

For a CHARACTER to NUMERIC conversion, you would use an INPUT function with the appropriate INFORMAT defined in the statement. Here's the DOC link on SAS.COM support website:

SAS 9.2 Language Reference: Dictionary - both INPUT function and SAS INFORMATs are discussed in this documentation material:
http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/titlepage.htm#

Scott Barry
SBBWorks, Inc.

Re: DATA FORMAT

Peter_C — Wed, 12 May 2010 11:32:58 GMT

provided by "DATA _NULL_", this most useful example was the only reference to DBSASTYPE in the discussion Forums, so I wanted to add my experience today 😞 - it took some time to appreciate that the column name must be defined in the excel context.
The column name in SAS was SBU_code, but using that caused the error message[pre] ERROR: Invalid column name specified in DBSASTYPE option: SBU_code[/pre]Took me a while to discover that there was no _ in the column name in excel.
The working syntax (abbreviated) [pre]data wrking.sasgla_sbu ;
set model2.'SAS_GLA_SBU'n( dbSAStype=( "SBU code"n='char(5)' ) );
run ; [/pre]
PeterC