topic Re: Database dropped leading zeros... in SAS Programming

Database dropped leading zeros...

EarlyCode — Mon, 25 Nov 2013 15:20:15 GMT

Slight annoyance on my part, but the database that I query has one field that is formatted as a number instead of text. As a result, when the value should be 0001234, it becomes 1234.

This is only a problem when I'm cross matching between the database and established reports that have the leading zeros. It won't get an exact match unless I change one or the other.

The preferred format that management likes to see is with the leading zeros.

So when running the compare queries, I export to excel to use the formula =RIGHT("000000"&A1,7)

This adds zeros on the front end, then only gives me last 7 numbers. (1234 becomes 0001234, While 12 becomes 0000012)

Is there a way to do this with a data step in SAS?

I've only found SUBSTR function, which seems to work more like the excel =MID() formula.

Thank you,

Re: Database dropped leading zeros...

snoopy369 — Mon, 25 Nov 2013 15:47:57 GMT

If it's stored as a numeric, and you work with it as a numeric, the leading zeroes are irrelevant. If you work with it as a character, ie with PUT(), you need to PUT it with the format z7. (or however wide it should be). Zw.d is the zero-padded format, so put(1234,Z7.) = 0001234.

Re: Database dropped leading zeros...

Jagadishkatam — Mon, 25 Nov 2013 15:48:17 GMT

Please try to use the format z8., it will display the data with leading zeros.

Thanks,

Jagadish

Re: Database dropped leading zeros...

EarlyCode — Mon, 25 Nov 2013 16:29:39 GMT

The field is "Package_No" and I will need to convert all pulled values to characters. But when I create a data step with Put(Package_No,Z7.); I get a syntax error

101 Put(Package_No,z7.)

ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, ), -, :, [, _ALL_,

_CHARACTER_, _CHAR_, _NUMERIC_, {.

ERROR 76-322: Syntax error, statement will be ignored.

Not sure what it's expecting.

Re: Database dropped leading zeros...

snoopy369 — Mon, 25 Nov 2013 16:32:46 GMT

What are you actually doing with the package_no variable? Post an example of the entire process - how you want to match it up, with some dummy data. You can't just 'put' something on its own, you have to assign that to a variable, or use a FORMAT statement (which changes how the variable is displayed, but not how it is used in sorts/merges).

Re: Database dropped leading zeros...

jaredp — Mon, 25 Nov 2013 16:38:59 GMT

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

Re: Database dropped leading zeros...

Peter_C — Mon, 25 Nov 2013 17:58:57 GMT

I think you have the solution to your SAS problem (format Z7.)

Just in case it is something you might need to do also in excel, the corresponding formula is =text(A1,"0000000"), where that "0000000" is the format in which to present the value in cell A1

With that formula you shouldn't lose the digits that you demonstrate in your example =RIGHT("000000"&A1,7)