merging many to many datasets with varying data

[ch835]

Hi all, am relatively news to SAS Programming and would like to check what would be the code for the possibility of obtaining the merged code as specified below?

TABLE A  (reference table)

Year          Identifier Code         Rating

2000                NM056            B

2002                NM056            A

2004                NM056            C

2005                NM056            B

2007                NM056            C

TABLE B

Year         Identifier Code         Alternative Code

2000                NM056            336

2001                NM056            336

2002                NM056            336

2006                NM056            336

2007                NM056            336

Looking at returning the following (merged code) 

TABLE A

Year                Identifier Code           Rating             Alternative Code

2000                NM056                        B                      336

2002                NM056                        A                      336

2004                NM056                        C                      336

2005                NM056                        B                      336

2007                NM056                        C                      336

*Note years are also based on Table A, reference table

Have tried the standard merge function by Table A and B, sorted by Identifier Code but the results are not as specified.. 

Thank you! 

[Tom]

There is no record in B for the third observation in A.  There is no YEAR=2004 in table B.

What value of "Alternative Code"n do you want to use for the YEAR 2004?

[ch835]

Sorry, think perhaps this example would be clearer:

Table A

IdentifierCode      Year      Rating 

CN045                  2000       B

CN045                  2001       C

CN045                  2003       A

Table B 

IdentifierCode      Year     Alternate Code

CN045                   2001    336

So basically after sorting both Tables A and B by identifier code, tried to merge both 

data tablemerged;

merge TableA TableB;

by IdentifierCode;

run;

(this is after PROC SORT of both tables A and B by IdentifierCode) 

The result is in tablemerged:

IdentifierCode      Year      Rating         Alternate Code

CN045                  2000       B

CN045                  2001       C

CN045                  2003       A

CN045                   2001                         336

However, would like the result to be following based on Table A:

IdentifierCode      Year      Rating    Alternate Code

CN045                  2000       B          336

CN045                  2001       C          336

CN045                  2003       A           336

So basically, just based on single line item, the alternate code can be populated into Table A based on Identifier Code.

Assuming that merging by Year variable (so looking for similar year 2000 with 2000) is not important; just match by IdentifierCode.. 

Thank you!

[Tom]

WHy does the second table have YEAR if you don't want to use it?

The values of IDENTIFIERCODE did not match. The two datasets have different values, even though they look to you like the same value.  What TYPE is the variable in each dataset.   The must be the same type or else your merge would fail.

Is it character in both?  Are the lengths the same?  Do the values in one of the dataset have leading spaces?  Does one of the dataset have a format attached that is truncating what is printed?  Do one or both of the datasets have non-visible characters in the field? Print or PUT some of the values using $QUOTE format to better see leading spaces or trailing invisible characters.  Print or put some of the values using the $HEX format to tell exactly what the invisible characters are.

If they are numeric in both then they must have a format attached.  Try printing the vlaues without any format attached to see what number is actually in the field. Or print using BEST32. format.

[Kurt_Bremser]

Will the "alternative code" be constant for a given identifier in dataset B, or can it change? If the latter, you need to have a rule for which one to select for an observation in A.