BookmarkSubscribeRSS Feed
wkossack_nspirehealth_com
Calcite | Level 5

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

13 REPLIES 13
AncaTilea
Pyrite | Level 9

Hi.

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

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

ballardw
Super User

Are you using the option Sheet_Interval='Bygroup' ?

wkossack_nspirehealth_com
Calcite | Level 5

yes I'm using sheet_interval='Bygroup'

Cynthia_sas
SAS Super FREQ

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
wkossack_nspirehealth_com
Calcite | Level 5

  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?

Reeza
Super User

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.

wkossack_nspirehealth_com
Calcite | Level 5

we are running 9.2

the log says

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

Cynthia_sas
SAS Super FREQ

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

Reeza
Super User

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

Cynthia_sas
SAS Super FREQ

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

wkossack_nspirehealth_com
Calcite | Level 5

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

wkossack_nspirehealth_com
Calcite | Level 5

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

rahulks
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1444 views
  • 0 likes
  • 6 in conversation