03-15-2013 04:46 PM
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
03-15-2013 06:43 PM
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.
length country $20;
if sex = 'F' then country = 'United States';
else country = 'United Kingdom';
proc sort data=new;
by country age;
ods tagsets.excelxp file='c:\temp\try_united.xml'
proc print data=new;
ods tagsets.excelxp close;
03-19-2013 01:38 PM
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?
03-19-2013 01:45 PM
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.
03-19-2013 02:27 PM
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.
03-19-2013 11:02 PM
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.
03-19-2013 03:24 PM
I tried this code and it produces only one tab
I even tried adding
where excltags_128 is an updated tagset
03-15-2013 05:31 PM
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.