Help using Base SAS procedures

DATA FORMAT

Reply
Contributor
Posts: 56

DATA FORMAT

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: DATA FORMAT

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.
New Contributor Ili
New Contributor
Posts: 3

Re: DATA FORMAT

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: DATA FORMAT

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.
Respected Advisor
Posts: 3,777

Re: DATA FORMAT

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]
Valued Guide
Posts: 2,175

Re: DATA FORMAT

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
Ask a Question
Discussion stats
  • 5 replies
  • 737 views
  • 0 likes
  • 5 in conversation