topic Re: how do i merge value from same column? in SAS Programming

how do i merge value from same column?

HazimHasanB — Tue, 27 Oct 2020 10:42:34 GMT

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.

Re: how do i merge value from same column?

Kurt_Bremser — Tue, 27 Oct 2020 10:58:25 GMT

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 !

Re: how do i merge value from same column?

HazimHasanB — Tue, 27 Oct 2020 11:25:10 GMT

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

Re: how do i merge value from same column?

Kurt_Bremser — Tue, 27 Oct 2020 11:31:19 GMT

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.

Re: how do i merge value from same column?

HazimHasanB — Tue, 27 Oct 2020 11:36:28 GMT

Re: how do i merge value from same column?

Kurt_Bremser — Tue, 27 Oct 2020 11:42:55 GMT

And PROC CONTENTS?

Re: how do i merge value from same column?

HazimHasanB — Tue, 27 Oct 2020 11:50:58 GMT

Re: how do i merge value from same column?

Kurt_Bremser — Tue, 27 Oct 2020 12:17:20 GMT

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).

Re: how do i merge value from same column?

Ksharp — Wed, 28 Oct 2020 05:00:03 GMT

Or Try start with operator EQT .

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

Re: how do i merge value from same column?

ballardw — Wed, 28 Oct 2020 05:14:34 GMT

@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.

Re: how do i merge value from same column?

HazimHasanB — Wed, 28 Oct 2020 18:09:58 GMT

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