The SAS Output Delivery System and reporting techniques

Moving data from SAS to Excel spreadsheet data is truncated?

Accepted Solution Solved
Reply
Occasional Contributor JGS
Occasional Contributor
Posts: 7
Accepted Solution

Moving data from SAS to Excel spreadsheet data is truncated?

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 ;


Accepted Solutions
Solution
‎02-03-2014 01:02 PM
Respected Advisor
Posts: 3,777

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

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

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

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

Solution
‎02-03-2014 01:02 PM
Respected Advisor
Posts: 3,777

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

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

Occasional Contributor JGS
Occasional Contributor
Posts: 7

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

Thank you.

That seems to be the issue.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 602 views
  • 3 likes
  • 3 in conversation