topic Re: Problem importing excel sheet into SAS in New SAS User

Problem importing excel sheet into SAS

J-Ann — Thu, 01 Oct 2020 20:14:56 GMT

Hi,

I'm trying to import this dataset into SAS (it's the NHIS 2019 adult dataset). I downloaded it as a csv, but have been having trouble importing csv files into SAS, so I converted it into an excel file, which I've had success with in the past.

This is my code:

libname nh "C:\users\Jodie-Ann\Documents\ile";

proc import out=nhis
datafile = "c:\users\\Jodie-Ann\Documents\ile\adult2019.xlsx"
dbms = excel replace;
sheet = "sheet2$";
range = "A1:TN31998";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;

run;

When I run I get this error:

WARNING: SHEET name will be ignored if conflict occurs with RANGE name specified.
ERROR: File _IMEX_.'sheet2$A1:TN31998'n.DATA does not exist.
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.44 seconds
cpu time 0.25 seconds

I've used this code before with a different data set that I converted from csv to excel, but not sure why it's not working this time. Any help would be appreciated, thank you!

Re: Problem importing excel sheet into SAS

CurtisMackWSIPP — Thu, 01 Oct 2020 20:20:09 GMT

Is there a sheet in this spreadsheet named "sheet2"? That is first thing to look for.

Re: Problem importing excel sheet into SAS

J-Ann — Thu, 01 Oct 2020 20:22:08 GMT

Hi,
Yes Sheet2 is the one with all the data-points.

Re: Problem importing excel sheet into SAS

SASKiwi — Thu, 01 Oct 2020 20:26:29 GMT

What happens if you don't use the RANGE statement and if you call the sheet just Sheet2 (no dollar sign on the end)?

Re: Problem importing excel sheet into SAS

Reeza — Thu, 01 Oct 2020 20:26:43 GMT

It's almost always easier to import CSV files and I would recommend you switch back. The quick lazy "trick" is to first run PROC IMPORT on your CSV file and then copy the code from the log and customize/correct it as needed. Most government data is designed to be imported via SAS so I'd give that a shot if you can. If you have issues, it is a public file so post the link and someone can likely help with the code needed to read the CSV.

Re: Problem importing excel sheet into SAS

ballardw — Thu, 01 Oct 2020 20:41:40 GMT

If you don't check very closely you can find that Excel when saving a CSV can significantly change values. Really, like changing hyphenated values like 5-1, that should be character, into dates, and stripping leading zeros that are part of things like account numbers.

Please describe the exact problems you have been having with importing CSV files. One important consideration is that Proc Import only examines 20 rows of data by default before setting variable properties. So use of setting a large value for the Guessingrows option often fixes many of the issues people encounter.

Re: Problem importing excel sheet into SAS

Reeza — Thu, 01 Oct 2020 20:48:47 GMT

Don't they provide the INPUT code for these files?
https://www.cdc.gov/nchs/nhis/2019nhis.htm

Using that with the CSV should get you what you need...

Re: Problem importing excel sheet into SAS

J-Ann — Thu, 01 Oct 2020 20:52:52 GMT

I tried again with the csv file and it worked! I'm not sure what was happening with it the first time I tried, but I've got it now! Thanks everyone