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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
ballardw
Super User

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.

Ody
Quartz | Level 8 Ody
Quartz | Level 8

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?

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ody
Quartz | Level 8 Ody
Quartz | Level 8

@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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

ballardw
Super User

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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