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

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

View solution in original post


All Replies
Regular Contributor
Posts: 244

Re: Database dropped leading zeros...

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

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.

Regular Contributor
Posts: 244

Re: Database dropped leading zeros...

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

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

Trusted Advisor
Posts: 1,131

Re: Database dropped leading zeros...

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

Thanks,

Jagadish

Thanks,
Jag
Valued Guide
Posts: 2,175

Re: Database dropped leading zeros...

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.

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

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