I am using TRANWRD and INDEX, but some cells are not concatenating.

[bn820]

Hello.

  I am trying to merge two variables by replacing the word OTHERcin one column with the content of another column. I am using the code below, but I have 3-4 cells that are blank (They didn't merge both columns). I can't seem to figure out what's the problem with those specific cells.

DATA WANT(RENAME=(AFORF2=AFORF_H_OF1));; /*THE NEW RISK HISTORY COLUMN IS AFORF_H_OF1*/
set HAVE;
if index(upcase(AFORF_H_OF),'OTHER') THEN
DO ;
AFORF1=cats(TRANSTRN(AFORF_H_OF,"Other",trimn('')),'|',Risk_H);;
AFORF2=TRANWRD(AFORF1,'||','|');
END;
ELSE AFORF2=AFORF_H_OF;
DROP AFORF1;

if index(upcase(AFORF_C_PREGN1),'OTHER') THEN
DO ;
AFORF_C1=cats(TRANSTRN(AFORF_C_PREGN1,"Other",trimn('')),'|',C_Risks);;
AFORF_C2=TRANWRD(AFORF_C1,'||','|');
END;
ELSE AFORF_C2=AFORF_C_PREGN1;/*THE NEW CURRENT RISKS COLUMN IS AFORF_C2*/
DROP AFORF_C1;
RUN;

[PaigeMiller]

Can we see a small portion of the actual data set that illustrates the problem? Please provide the data as working SAS data step code, and not via any other method. (Instructions and examples).

[bn820]

[PaigeMiller]

I asked to see a portion of the data, and gave instructions how to provide it.

[ballardw · Accepted solution]

Did you verify that values of AFORF1 and AFORF_C1 were as expected before dropping them?

How long are the values of AFORF_H_OF, Risk_h , AFORF_C_PREGN1 and C_risks ?

If the pieces are long enough then your use of CATS may be truncating because you have not defined a length of the variables AFORF1 and AFORF_C1 which means they default to maximum of 200 characters. So it might appear that they aren't concatenating because you didn't allow room in the resulting variable to hold the values. The AFORF2 and AFORF_C2 would inherit the lengths of the base variables.

[bn820]

Thank you! I defined the length of AFORF1, and it worked.