topic Re: Keep leading zeros in stored process in SAS Programming

Keep leading zeros in stored process

u38109150 — Thu, 10 Oct 2019 11:09:49 GMT

I create a repport with a stored process.
I use SQL.
I run this process with reports on in sas add-in for Excel.
In this reports are numbers beginnen with zero, like 000048383.
I will keep this zeros.
I think i should slove this in the stored proces tab "Data Sources and Targets. But I do'nt know how.

My code is like this.

VESTIGINGSNR is like 000047178622 format $12.

PROC SQL;
SELECT DISTINCT
FORMULE.HANDELSNAAM label="Handelsnaam",
FORMULE.NAW label="Adres",
FORMULE.VESTIGINGSNUMMER AS VESTIGINGSNR label="Vestigingsnummer"
FROM AIDDWH.REF_FORMULEBEDRIJF AS FORMULE
;
QUIT;

Re: Keep leading zeros in stored process

MichaelLarsen — Thu, 10 Oct 2019 10:38:05 GMT

Just add FORMAT=Z9. ie.:
label="Vestigingsnummer" format=z9.

Re: Keep leading zeros in stored process

u38109150 — Thu, 10 Oct 2019 11:08:34 GMT

Thank you for replying. This doesn't help. My input (VESTIGINGSNUMMER) is a character ($12.). Excel makes it a number, but it must stay a character.

Re: Keep leading zeros in stored process

MichaelLarsen — Thu, 10 Oct 2019 11:15:51 GMT

I have not tested it, but can you try this:

PROC SQL;
SELECT DISTINCT
FORMULE.NAAM label="Nalevingssoort",
FORMULE.HANDELSNAAM label="Handelsnaam",
FORMULE.NAW label="Adres",
catt("'",FORMULE.VESTIGINGSNUMMER AS VESTIGINGSNR) label="Vestigingsnummer"
FROM AIDDWH.REF_FORMULEBEDRIJF AS FORMULE
;
QUIT;

I am assuming that the leading zeroes are already present in the input table.

You can also take a look at the ODS EXCEL documentation it offers you more flexibility:

https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVersion=9.4&locale=en

Re: Keep leading zeros in stored process

MichaelLarsen — Thu, 10 Oct 2019 11:24:01 GMT

sorry there is a syntax error, try this:

PROC SQL;
SELECT DISTINCT
FORMULE.NAAM label="Nalevingssoort",
FORMULE.HANDELSNAAM label="Handelsnaam",
FORMULE.NAW label="Adres",
catt("'",FORMULE.VESTIGINGSNUMMER) AS VESTIGINGSNR label="Vestigingsnummer"
FROM AIDDWH.REF_FORMULEBEDRIJF AS FORMULE
;
QUIT;

Re: Keep leading zeros in stored process

u38109150 — Thu, 10 Oct 2019 11:40:16 GMT

Yes Thank you this works.

And I don't see the qoute (') in the Excel output.

Re: Keep leading zeros in stored process

Tom — Thu, 10 Oct 2019 12:53:38 GMT

Why doesn't it know that the variable is character and keep the results in Excel as character?

Is there something broken in the SAS Add-in to Excel?

Re: Keep leading zeros in stored process

MichaelLarsen — Thu, 10 Oct 2019 12:59:17 GMT

No, I think Excel is just trying to be helpful.
You can try and enter the value 000048383 into an empty cell and you will see the same problem.
Adding the quote as the first character forces Excel to regard the value as a character value.

Re: Keep leading zeros in stored process

Tom — Thu, 10 Oct 2019 13:08:14 GMT

@MichaelLarsen wrote:
No, I think Excel is just trying to be helpful.
You can try and enter the value 000048383 into an empty cell and you will see the same problem.
Adding the quote as the first character forces Excel to regard the value as a character value.

If I create an Excel file from normal SAS using tools like PROC EXPORT or XLSX engine on a libname it knows the variable is character and I don't have to play tricks with the values to get Excel to display it properly.

And in Excel itself if I set the type for a cell as text and enter a value with leading zeros it displays them properly.

So the question is why does pulling data with the Excel Add-In not also know that the variable being pulled is character?

Re: Keep leading zeros in stored process

TimBeese — Thu, 10 Oct 2019 13:57:51 GMT

This is a tricky one where there are two schools of thought on how this should work.

A) The data is marked as character data, so when it goes into Excel, we should treat it as text. This is the ideal solution for the user who wants to see their data in Excel exactly as they see it in their generated results.

B) The data is marked as character data, but Excel can interpret it differently, we should let Excel interpret it. This is the ideal solution for someone who wants to continue to explore their data with Excel. If Excel is able to interpret the data as a number or date, then the user can use Excel formulas to continue to work with their data.

As with many of these situations, we decided to add an option in the SAS Add-In for Microsoft Office to handle this. If you set your result format to SAS Report Xml, then the add-in has control over the format of the cell. We decide whether to force the format to Text or leave it as General based on an option.

Here's how to get to this option:

From the SAS tab on the ribbon, go to Tools->Options. On the options dialog go to the Results page and scroll to the bottom. There's a group called "Tables". Inside of that group is an option called "Allow Excel to interpret value types in SAS Report tables". By changing the value of this option, you can get the results you desire the next time you open a new instance of that stored process.

If you have content already inserted, then you'll need to either remove it and insert a new instance of the stored process. Or change the settings for that content. You can do this by setting the format of the cells to Text manually. Then, while that content is selected, choose SAS->Properties. On the Properties dialog, choose the Appearance tab and check "Preserve Excel number formatting". This will instruct us to NOT change the format on the cells when refreshing this content. You can set it up how you want it and the Add-In will not change it during a refresh.

Hope this helps!

Tim Beese

Re: Keep leading zeros in stored process

u38109150 — Thu, 10 Oct 2019 14:08:55 GMT

Yes, thank you, this helps too.

This is even a better solution.

My Users need to do the same thing. That won’t be a problem I suppose.

Re: Keep leading zeros in stored process

Tom — Thu, 10 Oct 2019 14:15:03 GMT

Much better answer.

Re: Keep leading zeros in stored process

Tom — Thu, 10 Oct 2019 14:20:10 GMT

You tagged the wrong answer as the solution.