BookmarkSubscribeRSS Feed
Fred_Gavin
Calcite | Level 5
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.
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Ili
Calcite | Level 5 Ili
Calcite | Level 5
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19
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]
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2010 views
  • 0 likes
  • 5 in conversation