BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EarlyCode
Fluorite | Level 6

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,

1 ACCEPTED SOLUTION

Accepted Solutions
jaredp
Quartz | Level 8

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

View solution in original post

6 REPLIES 6
snoopy369
Barite | Level 11

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.

EarlyCode
Fluorite | Level 6

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.

snoopy369
Barite | Level 11

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

jaredp
Quartz | Level 8

data _null_;

package_no=1234;

package_char = put(package_no, z7.);

put package_char;

run;

Jagadishkatam
Amethyst | Level 16

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

Thanks,

Jagadish

Thanks,
Jag
Peter_C
Rhodochrosite | Level 12

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)

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
  • 6 replies
  • 1335 views
  • 7 likes
  • 5 in conversation