Hi guys,
I have a problem when running this code to generate SQL data into report text format because it has a spacing problem in which sentences or words are being separated onto separate lines instead of flowing smoothly across (sample attached). I know it is a concatenation issue and have tried the options ls (linespace) and options ps (pagesize) but they have not helped. Does anyone have any suggestions?
Many thanks,
*Bringing in SQL tables into SAS format;
**Set DBMAX to longest character length in data field;
libname testsql oledb
init_string="Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=True;
Initial Catalog = 201107014D;
Data Source=vhacdwrb01.vha.med.va.gov"
Schema= dflt
DBMAX_Text= 10000;
*Getting sample into SAS dataset;
data batch3;
set testsql.TIU_Batch3_wText;
keep Source Sta3n DocumentIEN ReportText;
run;
*Exporting SAS dataset to txt files;
data _null_;
set batch3;
_filename= cats("\\vhacdwfpcfs02\Projects\201107014D\Annotation\TIU Reports\Batch 3\",Source,"_",Sta3n, "_",DocumentIEN)||'.txt';
file dummy filevar= _filename;
put Source= ;
put;
put Sta3n= ;
put;
put DocumentIEN= ;
put;
put ReportText= ;
run;
I would have thought that thlog would have a message about the wrapping. Very curious.
Your filename does not specify a record length, LRECL, but the libref specifies max length=10000. As I recall, the default output record length is 256 and wrapping occurs after that. Maybe you need to add LRECL=10000 to your fileref so that it looks like
file dummy filevar= _filename lrecl=10000;
If this doesn't solve the problem, perhaps provide a few records from your work.batch3 data set for us to play with.
The output you're giving us in the attachment hasn't been created with the code you've posted. So I'm not really sure what I'm looking at.
To use multiple 'put' statements writing text onto the same line of output you need to use syntax like 'put <variable> @;' - else each put statement will start on column 1 of a new line.
But I actually assume what disturbes you is that the output of variable 'ReportText' breaks over multiple lines. Have you checked that these line breaks are not already in the source data?
Hi Patrick,
Thanks for your response. Yes you are right, the Report Text being separated across multiple lines is the issue. I have checked that the source data is perfectly normal so I know it definitely has something to do with the code. I have tried putting the put <variable> @; as you suggested but it did not return favorable results.
Anyone encounter similar problems before?
Thanks,
Jane
Use the LRECL option on the FILE statement to set the line length. The default is only 256.
I would have thought that thlog would have a message about the wrapping. Very curious.
Your filename does not specify a record length, LRECL, but the libref specifies max length=10000. As I recall, the default output record length is 256 and wrapping occurs after that. Maybe you need to add LRECL=10000 to your fileref so that it looks like
file dummy filevar= _filename lrecl=10000;
If this doesn't solve the problem, perhaps provide a few records from your work.batch3 data set for us to play with.
Thank you for your clear and concise explanation! It appears to be wrapping correctly now
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.