how do i merge value from same column?

[HazimHasanB]

the case is under "relationship" column, there are "Husband" and "Wife". i want to change "Husband" and "Wife" into "Married".

PROC SQL;
UPDATE WORK.IMPORT
SET Relationship ='Married'
WHERE Relationship ='Husband';

i tried to do it like that, but then there is no rows updated.

please help me

Many thanks.

Best regards,

Hazim.

[Kurt_Bremser]

Works for me:

data have;
input Relationship :$20.;
datalines;
Married
Divorced
Unmarried
Husband
;

proc sql;
update have
  set Relationship ='Married'
  where Relationship ='Husband'
;
quit;

proc print data=have noobs;
run;

Result:

Relationship
Married
Divorced
Unmarried
Married

Take care of spelling, Husband <> husband !

[HazimHasanB]

yes i know it should be perfectly fine right, but here i tried it again but still no rows were updated

 

[Kurt_Bremser]

Then it is in your data; please run

proc contents data=work.import;
run;

proc freq data=work.import;
tables relationship;
run;

and post the results here.

The above two steps are essential for Maxim 3: Know Your Data.

[HazimHasanB]

[Kurt_Bremser]

And PROC CONTENTS?

[HazimHasanB]

[Kurt_Bremser · Accepted solution]

The you need to very carefully inspect the values that are actually stored in the dataset.

Run

data test;
set import;
rel_hex = put(relationship,$hex30.);
run;

to see if you have any specialties in the strings (leading blanks, undisplayable characters).

[HazimHasanB]

Thank you sir, found out there is actually a space behind every value.

[ballardw]

---

@HazimHasanB wrote:

---

One thing you need to be aware of is Proc Freq, and many procedures, with display character values left justified and remove leading spaces. It may be that your "Husband" should be " Husband" or "  Husband" in a comparison.

Example:

data junk;
   Relationship="            Husband";
run;

proc freq data=junk;
   tables relationship;
run;

You might try  adding a variable with the length of the relationship to see if the displayed length matches the expected:

data junk2;
   Relationship="            Husband";
   rlength=length(relationship);
run;

proc freq data=junk2;
   tables relationship*rlength/list ;
run;

If you observe this behavior then you can use either Left or Strip function to remove the leading spaces.

[Ksharp]

Or Try start with operator EQT .

proc sql;
update have
set Relationship ='Married'
where Relationship eqt 'Husband'
;
quit;