Hello, I am a SAS newbie (3 months).
In my project, I have 3 imported data sets that I need to join together on a primary key that is a numeric type. My challenge is that in two of the tables, the leading zeros do not exist, so the join will not match for those records without the leading zeros.
Example:
Table1
001001
001002
12345
54321
Table2
1001
1002
12345
54321
Table3
1001
1002
12345
54321
What can I do to add the leading zeros to the values that are missing leading zeros? The fixed length of the field is 5, so no leading zeros should be added to the values that have a non-zero number leading (e.g 12345, 10101).
Any help would be greatly appreciated!
Look at Z5. format.
Look at Z5. format.
Hi Reeza, thanks for your quick response. I am not sure I follow however.
Could you please elaborate? Thank you!
Have you actually run the join? If so was there an error or warning message? Are the variables actually of type numeric? Your comment about fixed length of 5 makes it sound that the values are likely string containing digits.
Proc contents data=table1;run; (or what ever the actual library.dataset name for table 1 would show whether actual numeric or character)
Are you trying to do this as a pass through to another database or are the tables both SAS datasets?
1. Numeric variables cannot have leading zeros, so it has to be a character variable.
2. You can convert the number to a character by using the SAS format Z5.
SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition
Then you can merge the fields together on the new character field or use the conversin in your PROC SQL step.
I'd be checking that the Table 1 import hasn't made your primary key a character variable first. If it's imported it as a number it shouldn't have the leading zeroes unless you've whacked a format on it. If that's the case convert it:
newvar = input(primary_key,5.) ;
Thank you everyone! I was able to resolve this by converting the number variable to character using the format Z5.
I appreciate everyones input. This was great.
@aga11940
Then please mark the question as answered.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.