The SAS Output Delivery System and reporting techniques

ods output to excel with tagset.excelxp

Reply
Contributor
Posts: 73

ods output to excel with tagset.excelxp

I have some data that I am putting into an excel spreadsheet

I'm using proc print; by country;

the problem is I have 'United States' and 'United Kingdom' as two of my countries but they are both ending up on a tab labeled 'United'

short of sticking an underscore and making the countries 'United_states' is there a way to get the full

Super Contributor
Posts: 543

Re: ods output to excel with tagset.excelxp

Hi.

When you do proc print by country, what do you expect to get really?

Why do you need the "BY"...?

Super User
Posts: 10,538

Re: ods output to excel with tagset.excelxp

Are you using the option Sheet_Interval='Bygroup' ?

Contributor
Posts: 73

Re: ods output to excel with tagset.excelxp

yes I'm using sheet_interval='Bygroup'

SAS Super FREQ
Posts: 8,744

Re: ods output to excel with tagset.excelxp

Hi:

  I do not observe that behavior. With a big enough length for COUNTRY, I get 2 bygroups (using fake data). See the screenshot. If you run my program and you still only get one sheet, then I would recommend that you open a track with Tech Support.

cynthia

data new;

  length country $20;

  set sashelp.class;

  if sex = 'F' then country = 'United States';

  else country = 'United Kingdom';

run;

proc sort data=new;

by country age;

run;

ods tagsets.excelxp file='c:\temp\try_united.xml'

    options(sheet_interval='bygroup');

 

  proc print data=new;

  by country;

  run;

ods tagsets.excelxp close;


try_united.png
Contributor
Posts: 73

Re: ods output to excel with tagset.excelxp

  I’m wondering this

I have other countries such as Japan and Germany….the length of the
field is $20.

I remember running into this problem before at another job but I can’t
remember what the solution is.  Is there a way to force the length of the label?

Super User
Posts: 17,912

Re: ods output to excel with tagset.excelxp

What version of SAS and Tagsets do you have? I find that a lot of tagset errors that can't be replicated are due to using a different version of the tagset.

Assuming you're on the latest version you can try and modify the variable size in a previous step, though both United States and United Kingdom appear less than 20 to me.

Contributor
Posts: 73

Re: ods output to excel with tagset.excelxp

we are running 9.2

the log says

Compatible with SAS 9.1.3 and above, v1.128, 10/24/2011

SAS Super FREQ
Posts: 8,744

Re: ods output to excel with tagset.excelxp

Hi:

  I am guessing that

1) your data are not sorted by country or

2) somehow your code is generating errors or

3) possibly you have codes, like GB for United Kindom and US for United States and DE for Germany and you have formats for the long name and somehow the format is broken or specified or built incorrectly.

  Did you run my test code? If my code worked for you and you got a tab for United States (length 13) and a tab for United Kingdom (length 14), then the issue is not with TAGSETS.EXCELXP. The message that you showed about TAGSETS.EXCELXP being compatible with SAS 9.1.3 really is irrelevant to the issue you observe, in my opinion.

  I doubt there is a problem with your ExcelXP or version of SAS. If you cannot post data to the forum, then I would recommend that you open a track with Tech Support so they can look at ALL your code and ALL your data and help you figure out where the problem is occuring.

cynthia

Super User
Posts: 17,912

Re: ods output to excel with tagset.excelxp

Cynthia,

Your code cuts off for me like the OP indicates. I'm using Excel 2010 if that matters, from SAS 9.2, with Tagsets 1.127.

Excel tabs 201.JPG

SAS Super FREQ
Posts: 8,744

Re: ods output to excel with tagset.excelxp

Interesting, I ran my code in SAS 9.3, using Office 2010 and the tagset template dated:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011).

So, any level of tagset template can be used, but it's possible that the XML generated is slightly different, depending on your version of SAS. The xml in my output file is

<Worksheet ss:Name="country=United Kingdom">

<Worksheet ss:Name="country=United States">

The Excel sheet name can be 31 or 32 characters, so even with country= there should be enough characters for the string.

I would say that this is probably something to bring to the attention of Tech Support. I attached a screen shot of my output, using the above versions of SAS, Microsoft and the tagset template. At this point, Tech Support are the only folks with 9.2 to test on. I don't know what's going on and I no longer have 9.2 to run a parallel test.

cynthia

Contributor
Posts: 73

Re: ods output to excel with tagset.excelxp

I opened a ticket with Tech support and finally they told me they were able to reproduce my problem...a solution should be coming soon

Contributor
Posts: 73

Re: ods output to excel with tagset.excelxp

I tried this code and it produces only one tab

I even tried adding

path(prepend) work.templat(update);


include "excltags_128";

where excltags_128 is an updated tagset

Occasional Contributor
Posts: 14

Re: ods output to excel with tagset.excelxp

Hi,

As per my thinking, you are talking about PROC sort and the problem you are facing is may be defined length. First see the length of country variable,increase the length size and you will get the full country name.

Ask a Question
Discussion stats
  • 13 replies
  • 553 views
  • 0 likes
  • 6 in conversation