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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
7 REPLIES 7
Reeza
Super User

Look at Z5. format. 

aga11940
Calcite | Level 5

Hi Reeza, thanks for your quick response. I am not sure I follow however.

Could you please elaborate? Thank you!

ballardw
Super User

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?


Reeza
Super User

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.

SteveNZ
Obsidian | Level 7

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

aga11940
Calcite | Level 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.

Patrick
Opal | Level 21

@aga11940

Then please mark the question as answered.

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