Community talk about communities.sas.com. Meta!

CSV to SAS import is trimming variable responses

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

CSV to SAS import is trimming variable responses

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;

 


Accepted Solutions
Solution
‎03-15-2017 03:26 PM
Super User
Posts: 10,543

Re: CSV to SAS import is trimming variable responses

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


All Replies
Solution
‎03-15-2017 03:26 PM
Super User
Posts: 10,543

Re: CSV to SAS import is trimming variable responses

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.

 

 

Occasional Contributor
Posts: 16

Re: CSV to SAS import is trimming variable responses

Thank you very much for your help!
☑ This topic is solved.

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

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