turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How to Merge these tables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 12:51 PM

Table1 Table2

NUM NUM

12345678 012345678

9876543 009876543

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 01:12 PM

Are the columns numeric or character?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 01:16 PM

What do you mean by 'MERGE'? What is your hoped for result?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 01:46 PM

Do you just need to add leading zeros? This DATA step creates a second variable with leading zeros.

data have(drop=len);

length x1 x2 $9;

input x1 $;

x2='000000000';

len=length(x1);

substr(x2,9-len+1) = x1;

datalines;

1

1234

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 02:43 PM

Let's break down the code a bit:

First I am going to initialize X2 to all zeros.

x2='000000000';

We will need to know how many characters are in X1 (there are other ways to count characters, but this is easy).

len=length(x1);

The SUBSTR function can be used on either the left or right of the equal sign. When it is used on the right it extracts characters from the string. When it is used on the left it inserts characters. Here we want to insert the characters contained in X1 into X2. This replaces corresponding preloaded zeros. The second argument (9-len+1) tells SUBSTR where to start the insertion. I could have written this argument as 10-len.

substr(x2,9-len+1) = x1;

Hopefully X2 can now be used to match the variables in the other data set that already have the leading zeros. I am assuming that the numbers themselves are some kind of coded value. It is, therefore, probably safer to leave them as character strings. Merging using numeric values can have some unintended consequences (there are rounding and storage issues).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 04:14 PM

To left fill '0's, aside from ArtC's igneous left-substr() approach (BTW, Art and all, are there any other functions can be applied like this? I can't recall any), here is another more conventional approach (borrowing Art's data):

data have;

length x1 x2 $9;

input x1 $;

x2=put(input(x1,best.),z9.);

datalines;

1

1234

run;

Haikuo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 05:12 PM

So why convert back to character after converting to numeric? Why not just stop at

x2=input(x1,best.);

and do the merge using numeric values?

That's the basis for the proc sql statement discussed above.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2013 05:38 PM

Well, according to OP's post (3rd one from top), both x1 and x2 are char.

Haikuo