BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
glcoolj12
Obsidian | Level 7

I have an .XLSX document with two sheets - sheet1 and sheet2.  I saved both sheets as .CSV files.  I'm using SAS9.4.

 

I'm having an issue importing a sheet1.csv file into SAS.  The import itself works, but it's cutting off responses for one variable. For example, a response for CONDITION in the .csv file is "to treat allergies" will be imput as "to treat aller".  I've attached the code that SAS import wizard produced when importing this file.  

 

I don't have this issue when I import sheet2.csv file - the response for CONDITION do not get cutoff.  For example, "cough and to control asthma" is imput as "cough and to control asthma".

 

Both .csv files come from the same document, I don't know why the responses are getting cut off for CONDITION in sheet1.

 

Any help would be much appreciated!

 

 

PROC IMPORT out=work.sheet1
datafile = "path\sheet1.csv"
DSMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

PROC IMPORT out=work.sheet2
datafile = "path\sheet2.csv"
DSMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

You need to add a GUESSINGROWS option to the import statement, preferrably as large as the number of rows in the source file or 32767, which ever is smaller.

Otherwise the default is to look at the first 20 rows of data to guess the type of a variable and set things like the maximum length of character variables.

 

If the two sheets have the same structure you may want to look at the log after importing them to see the differences in datastep code generated by proc import for the two sets. You could copy one of the data step code blocks to the editor and change the lengths and such in the informats to the longest value in either and by changing the infile and data set read both with the same charateristiecs.

 

 

View solution in original post

2 REPLIES 2
ballardw
Super User

You need to add a GUESSINGROWS option to the import statement, preferrably as large as the number of rows in the source file or 32767, which ever is smaller.

Otherwise the default is to look at the first 20 rows of data to guess the type of a variable and set things like the maximum length of character variables.

 

If the two sheets have the same structure you may want to look at the log after importing them to see the differences in datastep code generated by proc import for the two sets. You could copy one of the data step code blocks to the editor and change the lengths and such in the informats to the longest value in either and by changing the infile and data set read both with the same charateristiecs.

 

 

glcoolj12
Obsidian | Level 7
Thank you very much for your help!

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!

Discussion stats
  • 2 replies
  • 2507 views
  • 0 likes
  • 2 in conversation