topic Re: Concatenation/Spacing Issue in ODS and Base Reporting

Concatenation/Spacing Issue

JY2013 — Mon, 05 Aug 2013 23:03:20 GMT

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;

Re: Concatenation/Spacing Issue

Patrick — Tue, 06 Aug 2013 12:12:29 GMT

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?

Re: Concatenation/Spacing Issue

JY2013 — Tue, 06 Aug 2013 15:15:45 GMT

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

Re: Concatenation/Spacing Issue

Tom — Tue, 06 Aug 2013 15:36:06 GMT

Use the LRECL option on the FILE statement to set the line length. The default is only 256.

Re: Concatenation/Spacing Issue

bentleyj1 — Tue, 06 Aug 2013 15:46:10 GMT

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.

Re: Concatenation/Spacing Issue

JY2013 — Tue, 06 Aug 2013 16:06:14 GMT

Thank you for your clear and concise explanation! It appears to be wrapping correctly now