topic Re: ods output to excel with tagset.excelxp in ODS and Base Reporting

ods output to excel with tagset.excelxp

wkossack_nspirehealth_com — Fri, 15 Mar 2013 20:46:19 GMT

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

Re: ods output to excel with tagset.excelxp

AncaTilea — Fri, 15 Mar 2013 21:02:16 GMT

Hi.

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

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

Re: ods output to excel with tagset.excelxp

ballardw — Fri, 15 Mar 2013 21:27:23 GMT

Are you using the option Sheet_Interval='Bygroup' ?

Re: ods output to excel with tagset.excelxp

rahulks — Fri, 15 Mar 2013 21:31:56 GMT

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.

Re: ods output to excel with tagset.excelxp

wkossack_nspirehealth_com — Fri, 15 Mar 2013 21:51:05 GMT

yes I'm using sheet_interval='Bygroup'

Re: ods output to excel with tagset.excelxp

Cynthia_sas — Fri, 15 Mar 2013 22:43:15 GMT

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;

Re: ods output to excel with tagset.excelxp

wkossack_nspirehealth_com — Tue, 19 Mar 2013 17:38:45 GMT

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?

Re: ods output to excel with tagset.excelxp

Reeza — Tue, 19 Mar 2013 17:45:06 GMT

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.

Re: ods output to excel with tagset.excelxp

wkossack_nspirehealth_com — Tue, 19 Mar 2013 18:16:41 GMT

we are running 9.2

the log says

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

Re: ods output to excel with tagset.excelxp

Cynthia_sas — Tue, 19 Mar 2013 18:27:47 GMT

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

Re: ods output to excel with tagset.excelxp

Reeza — Tue, 19 Mar 2013 18:59:32 GMT

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.

Re: ods output to excel with tagset.excelxp

wkossack_nspirehealth_com — Tue, 19 Mar 2013 19:24:17 GMT

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

Re: ods output to excel with tagset.excelxp

Cynthia_sas — Wed, 20 Mar 2013 03:02:09 GMT

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

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

Re: ods output to excel with tagset.excelxp

wkossack_nspirehealth_com — Wed, 20 Mar 2013 22:41:49 GMT

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