topic Re: One to many merge in SAS Programming

One to many merge

nithya_r99 — Mon, 13 Apr 2020 16:37:58 GMT

I am trying to merge, but instead of merging by my by variable, SAS instead vertically concatenates the datasets on top of each other

Re: One to many merge

SASKiwi — Sun, 12 Apr 2020 22:19:22 GMT

You get this behaviour because jobid and jobcode don't match exactly for any of your data. Start by making sure the two variables have the same length. Then look at one value that should match, but doesn't. For example one of variables might contain leading blanks.

Re: One to many merge

Kurt_Bremser — Mon, 13 Apr 2020 05:59:19 GMT

This is a classic case of Maxim 3: Know Your Data.

Leading blanks (as already mentioned) can cause this. You also could have a format assigned that visually creates the same value. $UPCASE will display "bagclk" as "BAGCLK", but the raw values won't match, and SAS uses those for a join.

Run a proc contents on your datasets first, then inquire further.

Re: One to many merge

nithya_r99 — Mon, 13 Apr 2020 14:04:16 GMT

I checked this, but both values have the same length and type. I even tried to create a new variable using Upcase, but i am still getting the same issue

Re: One to many merge

Tom — Mon, 13 Apr 2020 14:18:34 GMT

Find two values from the two different sources that look the same to you and display them using the $HEX format. Compare the values until you see a difference. Specific hexcodes to look for that will cause trouble because they are not normally displayed as any ink on the page are:

20 - space

09 - tab

0A - linefeed

0D - carriage return

A0 - non-breaking space

00 - null

FF - ???