topic Re: merging many to many datasets with varying data in SAS Programming

merging many to many datasets with varying data

ch835 — Thu, 17 Feb 2022 15:41:14 GMT

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!

Re: merging many to many datasets with varying data

Tom — Thu, 17 Feb 2022 15:50:00 GMT

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?

Re: merging many to many datasets with varying data

ch835 — Fri, 18 Feb 2022 02:14:52 GMT

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!

Re: merging many to many datasets with varying data

Tom — Fri, 18 Feb 2022 02:25:30 GMT

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.

Re: merging many to many datasets with varying data

Kurt_Bremser — Fri, 18 Feb 2022 06:41:37 GMT

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.