Moving data from SAS to Excel spreadsheet data is truncated?

JGS — Mon, 03 Feb 2014 17:14:51 GMT

Hello ;

My problem is that the data string that I would like to put into a cell in excel is getting truncated.

I have tried breaking the string up and using macro variables.

But the complete string does not show up in the cell in excel,.

The max length that I can generate is around 162 characters.

I am sure that I am over looking something, but I am not sure what.

Thank you for your help.

options mprint mlogic symbolgen msglevel=i orientation = landscape

nonumber nodate nofmterr ls=max lrecl=max ;

This code goes to the proc report.

Bring in the data etc.

if name = "&name1 " then do ; col3 = "&length1 " ; col4 = "&formatB1 "; col5 = "&CTerm1" ; end ;

if col6 = "Long text sting goes here" : end ;

ods tagsets.EXcelXP options (sheet_name="&dsnme"
                         frozen_headers='1'
                         autofit_height = 'yes')
file= "c:\sriprojects\&prjname\define_output\&dsnme..xml"
style=minimal ;

proc report data = &dsnme.E headline headskip split='|' missing
                  style(header)=[backgroundcolor=white ];
   column name label TypeC col3 col4 col5 col6 col7 col8 varnum ;
define varnum / order=data noprint ;
define name   / display 'Variable Name'      format= $20.   STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define label / display 'Variable Label'     format= $60.   STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define typeC / display 'Type'               format= $10.   STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define col3   / display 'Length'             format= $10.   STYLE(column)=[cellwidth=100 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define col4   / display 'Format '            format= $100. STYLE(column)=[cellwidth=100 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define col5   / display 'Controlled Terms ' format= $100. STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define col6   / display 'Origin'             format= $100. STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define col7   / display 'Role'               format= $100. STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];
define col8   / display 'Comments'           format= $100. STYLE(column)=[cellwidth=175 just=left vjust=top] style(header)=[font_weight=bold just=left vjust=top ];

run ;

ods tagsets.EXcelXP close;
ods listing ;

Re: Moving data from SAS to Excel spreadsheet data is truncated?

Cynthia_sas — Mon, 03 Feb 2014 18:01:37 GMT

Hi:

I can put huge blocks of text into a cell using PROC REPORT. There have been previous postings in the forum about this.

One thing I'd recommend is getting rid of your FORMAT= option, which can cause truncation.

cynthia

Re: Moving data from SAS to Excel spreadsheet data is truncated?

data_null__ — Mon, 03 Feb 2014 18:02:19 GMT

What happens if you remove those FORMAT= opions on the define statements?

Re: Moving data from SAS to Excel spreadsheet data is truncated?

JGS — Mon, 03 Feb 2014 18:47:19 GMT

Thank you.

That seems to be the issue.

topic Re: Moving data from SAS to Excel spreadsheet data is truncated? in ODS and Base Reporting

Moving data from SAS to Excel spreadsheet data is truncated?

Re: Moving data from SAS to Excel spreadsheet data is truncated?

Re: Moving data from SAS to Excel spreadsheet data is truncated?

Re: Moving data from SAS to Excel spreadsheet data is truncated?