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,
data _null_;
package_no=1234;
package_char = put(package_no, z7.);
put package_char;
run;
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.
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.
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).
data _null_;
package_no=1234;
package_char = put(package_no, z7.);
put package_char;
run;
Please try to use the format z8., it will display the data with leading zeros.
Thanks,
Jagadish
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)
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.
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.