topic Help with importing excel file into sas in SAS Procedures

Help with importing excel file into sas

violet77 — Wed, 23 Aug 2017 03:27:45 GMT

Hello. I am new to SAS. I created a dataset that has 750 observations and 14 variables in excel. I saved it as a .csv file and imported it into SAS using the import wizard. The dataset was created, but there are now 835 obersvations. I have missing values, which are entered as (.) in the observations 1 to 750. However, SAS added new observations all with missing values (.) across all 14 variables. Can anyone help me understand what I might have done wrong and/or why SAS added over 100 new observations all with missing values.

Thank you for you time and consideration.

Re: Help with importing excel file into sas

Reeza — Wed, 23 Aug 2017 04:20:38 GMT

You could just import the Excel file.

proc import out=myExcel file='path to excel' dbms=xlsx replace; sheet='Sheet1';run;

Another option, is in your PROC IMPORT set GUESSINGROWS to a really large number.

And another option, after you run the PROC IMPORT, check your log. Take that code and modify it as necessary. Make sure it matches what you expect from your data.

@violet77 wrote:

Hello. I am new to SAS. I created a dataset that has 750 observations and 14 variables in excel. I saved it as a .csv file and imported it into SAS using the import wizard. The dataset was created, but there are now 835 obersvations. I have missing values, which are entered as (.) in the observations 1 to 750. However, SAS added new observations all with missing values (.) across all 14 variables. Can anyone help me understand what I might have done wrong and/or why SAS added over 100 new observations all with missing values.

Thank you for you time and consideration.

Re: Help with importing excel file into sas

ballardw — Fri, 25 Aug 2017 17:26:12 GMT

The issue with the CSV is actually caused by Excel. I call these Phantom rows or columns.

If you enter data into a row or column in Excel and then delete the values Excel apparently keeps track of those as "used" rows or columns and exports them when saved to CSV.

One option is to open the csv in a text editor and delete the rows.

Another option is to use the code generated by proc import and modify it so that that you delete the row if all of the values, or if there is a variable or combination that should always be present, are missing

Re: Help with importing excel file into sas

violet77 — Fri, 25 Aug 2017 18:43:21 GMT

Hello.

Thank you very much for taking time to respond to my question.