SAS Procedures

Help using Base SAS procedures
BookmarkSubscribeRSS Feed
violet77
Calcite | Level 5

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.

3 REPLIES 3
Reeza
Super User

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.


 

ballardw
Super User

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

violet77
Calcite | Level 5
Hello.

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

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1849 views
  • 0 likes
  • 3 in conversation