DATA Step, Macro, Functions and more

Database dropped leading zeros...

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Database dropped leading zeros...

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,


Accepted Solutions
Solution
‎11-25-2013 11:38 AM
Contributor
Posts: 71

Re: Database dropped leading zeros...

Posted in reply to EarlyCode

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

View solution in original post


All Replies
Super Contributor
Posts: 253

Re: Database dropped leading zeros...

Posted in reply to EarlyCode

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.

Contributor
Posts: 30

Re: Database dropped leading zeros...

Posted in reply to snoopy369

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.)

                              -

                              22

                              76

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.

Super Contributor
Posts: 253

Re: Database dropped leading zeros...

Posted in reply to EarlyCode

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).

Solution
‎11-25-2013 11:38 AM
Contributor
Posts: 71

Re: Database dropped leading zeros...

Posted in reply to EarlyCode

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

Trusted Advisor
Posts: 1,137

Re: Database dropped leading zeros...

Posted in reply to EarlyCode

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

Thanks,

Jagadish

Thanks,
Jag
Valued Guide
Posts: 2,177

Re: Database dropped leading zeros...

Posted in reply to EarlyCode

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)

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 559 views
  • 7 likes
  • 5 in conversation