Text mining and content categorization

specify data type in proc import for excel file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

specify data type in proc import for excel file

Hi all,

I would like to import an excel file (all fields are formatted as "General") to SAS. The field, "Result", contains numerical values for some observations and character values for other observations. When I first imported the excel worksheet to SAS, I realized that the result field was imported solely as a numerical field (i.e., the character values for the result field all become missing).

 

So I tried to use the following code to specify that I would like to import "Result" as character field instead of numerical field. However, it does not seem like the code is working. Would someone please help me fix this issue? Thank you very much in advance!

 

proc import datafile="Z:\Positive Health Check\Data\1985-2017May Data\test2.xlsx" out=ts

DBMS=EXCEL replace;

DBDSOPTS= "DBTYPE=(RESULT='CHAR(20)')";

run;


Accepted Solutions
Solution
‎05-12-2017 12:33 AM
SAS Employee
Posts: 295

Re: specify data type in proc import for excel file

If you're using  SAS 9.4, then you can try the XLSX engine:

 

proc import datafile='test_Excel.xlsx'
  out=work.test_Excel
  replace
  dbms=xlsx;
  sheet='Sheet1';
run; quit;

 

Vince DelGobbo

SAS R&D

View solution in original post


All Replies
PROC Star
Posts: 7,416

Re: specify data type in proc import for excel file

First question is whether you can use the Excel engine. What code did you submit that resulted in the missing values?

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 10,843

Re: specify data type in proc import for excel file

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Occasional Contributor
Posts: 10

Re: specify data type in proc import for excel file

Thanks for your prompt response! By "does not work", I meant the field, "Result", is still imported as numerical variable (i.e., the character values were set to missing in my temporary sas dataset). Here I am attaching a testing dataset, which includes the three fields (patient, test_name, result). The result field can have either number of CD4 count or positive/negative test result, depending on the associated lab test name.  

 

The following text is the log when I ran my code:

 

617 proc import file="C:\Users\Enoch\Desktop\SAS 9.4\test_Excel.xlsx" out=test6
617! DBMS=Excel replace;
618 DBDSOPTS= "DBTYPE=(Result='CHAR(20)')";
619 run;

NOTE: WORK.TEST6 data set was successfully created.
NOTE: The data set WORK.TEST6 has 130 observations and 3 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.37 seconds
cpu time 0.12 seconds

 

The imported result field was set to missing for character values: 

 

result.PNG

PROC Star
Posts: 7,416

Re: specify data type in proc import for excel file

Since you can use the Excel engine, try to import it with the following code:

proc import datafile="C:\Users\Enoch\Desktop\SAS 9.4\test_Excel.xlsx" out=test6 replace dbms=excel;
  getnames=yes;
  mixed=yes;
  sheet='Sheet1';
  usedate=yes;
  scantime=yes;
run;

Art, CEO, AnalystFinder.com

Solution
‎05-12-2017 12:33 AM
SAS Employee
Posts: 295

Re: specify data type in proc import for excel file

If you're using  SAS 9.4, then you can try the XLSX engine:

 

proc import datafile='test_Excel.xlsx'
  out=work.test_Excel
  replace
  dbms=xlsx;
  sheet='Sheet1';
run; quit;

 

Vince DelGobbo

SAS R&D

Occasional Contributor
Posts: 10

Re: specify data type in proc import for excel file

Thanks! it works.

Occasional Contributor
Posts: 10

Re: specify data type in proc import for excel file

I tried you code, but the character value was still set to missing after import
Occasional Contributor
Posts: 10

Re: specify data type in proc import for excel file

Actually, your code works. I just realized that I had a typo in my coding before. Sorry for the confusion! Thanks for your helpful feedback!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 912 views
  • 1 like
  • 4 in conversation