DATA Step, Macro, Functions and more

Proc Export and Truncated Values or ODS and CR/LB

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 83
Accepted Solution

Proc Export and Truncated Values or ODS and CR/LB

Hello...

 

I have an annoying problem I'm hoping you can help me with.

 

I'm trying to create a TXT file but when I use proc export it's truncating values in my dataset. I'm pretty sure this is because SAS is setting the field length to the first length it comes to when exporting. What is the work around for this? 

 

Current code:

proc export

      data = report2

      outfile = "&directory\&sfile..TXT"

      DBMS = DLM;

      delimiter = "|";

run;

 

Alternately, I've tried using ODS to create the TXT file but when I do I get extra cairrage returns and line breaks at the end of the file that I can't figure out how to delete.

 

Current code:

ods listing close;

 

ods csv file = "&directory\&sfile..TXT"

options (missing = '' delimiter = '|');

 

proc print

data = report2

noobs;

run;

 

ods csv close;

 

 

I would LOVE some help with both issues however I'd gladly take either one over the other. 

 

Appreciate any insight! Thanks!


Accepted Solutions
Solution
‎10-20-2015 09:26 AM
Super User
Super User
Posts: 7,997

Re: Proc Export and Truncated Values or ODS and CR/LB

Its not your proc export (although I don't recommend using that).  I would say that the dataset you are exporting is where the truncation is happening.  

 

What I would suggest, save us guessing, is to post a simple example of your data - make it up if you like - just a few records, and the code you use to export it to a file.  We can then run it and see.  

 

The other thing to check, proc export actually generates a datastep, if you look in the log it will show you the generated code, make sure the length there matches the length in your dataset, and that the data is not truncated in the dataset.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Proc Export and Truncated Values or ODS and CR/LB

I don't see any truncation such as you describe when exporting text files with similar code.

You may want to make sure you have the REPLACE option set.

Another, check the properties of the variables using proc contents to see the lengths assigned to character variables. Possibly data went missing on import. This seems especially likely if you used proc import to read the data and even more so if the source file was Excel.

Something else to check is if the original data had imbedded CR/LF in open text.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Export and Truncated Values or ODS and CR/LB

I'm reading in the original file via an infile statement where I am specifying the informat and format of each field.

 

I join a few other fields to my dataset via proc sql, even specifying again the format of the fields being truncated prior to the proc export. I've opened the dataset and verified that the field lengths are set to the original lengths I specified.

 

Even with this, proc export is truncating some of my field values. I know it's because there are only a few values in the first 20 records for these fields and the rest are blank. Is there any way around this? Is there a "guessingrows = " option like there is for proc import?

 

Solution
‎10-20-2015 09:26 AM
Super User
Super User
Posts: 7,997

Re: Proc Export and Truncated Values or ODS and CR/LB

Its not your proc export (although I don't recommend using that).  I would say that the dataset you are exporting is where the truncation is happening.  

 

What I would suggest, save us guessing, is to post a simple example of your data - make it up if you like - just a few records, and the code you use to export it to a file.  We can then run it and see.  

 

The other thing to check, proc export actually generates a datastep, if you look in the log it will show you the generated code, make sure the length there matches the length in your dataset, and that the data is not truncated in the dataset.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Export and Truncated Values or ODS and CR/LB


RW9 wrote:

 

 

The other thing to check, proc export actually generates a datastep, if you look in the log it will show you the generated code, make sure the length there matches the length in your dataset, and that the data is not truncated in the dataset.


 

After sleeping on it and reading your reply it hit me.

 

Normally the proc export data step doesnt write to my log; I'll just get a few Note: lines letting me know the export was successful. I had to clear my log a few times then run the export to see the data step. After getting it to write to my log I was able to confirm that the data was being written to my text file correctly.

 

Where I erred was using proc import to check that the file was written properly. Because the first several lines of some of the fields are blank, and proc import uses the first 20 values to set the field length, I was getting truncated values. After adding the "guessingrows = " option the file was imported successfully. In hindsight I should probably stop using proc import; it's not the first time something like this has created frustration, lol.

 

I'm marking your answer as the correct answer because it helped me think through my mistake. Thanks. 

Super User
Super User
Posts: 7,997

Re: Proc Export and Truncated Values or ODS and CR/LB

And here is one of the fundamentals to always be remembered.  Don't use proc import/export.  Write the import export yourself, using pre-defined and agreed upon data transfer agreement.  This avoids the whole "guessing" aspect of those procedures.  

Super User
Posts: 11,343

Re: Proc Export and Truncated Values or ODS and CR/LB

In addition to https://communities.sas.com/t5/user/viewprofilepage/user-id/45151 if using Base SAS you might want to investigate using FSLIST to examine text files, especially if you have assigned a fileref. In the little SAS command box you can type:

FSLIST fileref; cols;

Which will open a window to view the file and will have a column ruler at the top. FSLIST is not an editor so there are no search or edits performed, it just shows the text appearing in the file.

Warning: It doesn't behave well if the fileref references more than a single external file.

The additional command NUM will show line numbers as well.

Or Proc FSLIST will get you there as well.

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 1060 views
  • 0 likes
  • 3 in conversation