## Lowcase and Strip functions

Solved
Occasional Contributor
Posts: 8

# Lowcase and Strip functions

Hi,

How can I use "lowcase" and "strip" functions in the below code on the common_variable?

thanks for the help in advance.

data AB AB_dummy;

merge A (in=a) B (in=b);

by common_variable;

if a then output AB;

if a and not b then output AB_dummy;

run;

Accepted Solutions
Solution
‎08-18-2014 02:37 AM
Posts: 4,742

## Re: Lowcase and Strip functions

You need to apply these functions before the merge step (and also before you sort the source tables).

All Replies
Solution
‎08-18-2014 02:37 AM
Posts: 4,742

## Re: Lowcase and Strip functions

You need to apply these functions before the merge step (and also before you sort the source tables).

Super User
Posts: 9,599

## Re: Lowcase and Strip functions

Hi,

Proc sql;   /* Note no need to pre-sort */

create table AB as

select     A.*,          /* Note I don't advocate the use of *, this is just as I don't know your variables */

B.*

from      A A

left join B B

on          lowcase(strip(A.COMMON_VARIABLE))=lowcase(strip(B.COMMON_VARIABLE));

create table AB_dummy as

select     A.*

from        A A

where     not exists(select THIS.COMMON_VARIABLE from B THIS

where THIS.COMMON_VARIABLE = A.COMMON_VARIABLE);

quit;

Occasional Contributor
Posts: 8

## Re: Lowcase and Strip functions

I have tried this option and it works perfectly.

However, I want to write my code in SAS not using proc sql.

Super User
Posts: 9,599

## Re: Lowcase and Strip functions

TBH I find SAS so long winded for these things.

data a;

set a;

common_variable=lowcase(common_variable);  /* Note the strip makes no difference in this case */

run;

data b...

proc sort a;

by common_variable;

run;

proc sort...

data AB AB_dummy;

merge A (in=a) B (in=b);

by common_variable;

if a then output AB;

if a and not b then output AB_dummy;

run;

Posts: 4,742

## Re: Lowcase and Strip functions

Under the assumption that the relationship between table A and table B is manyne below should also work:

data a;

length common_variable \$ 3;

do common_variable=' A','b','  c';

var_a+1;

output;

output;

end;

run;

data b;

length common_variable \$ 3;

do common_variable='a','C';

var_b+1;

output;

end;

run;

data AB AB_dummy;

if _n_=1 then

do;

dcl hash h1();

_rc=h1.defineKey('common_variable');

_rc=h1.defineData('var_b');

_rc=h1.defineDone();

do until(last);

set b end=last;

common_variable=lowcase(strip(common_variable));

_rc=h1.replace();

end;

end;

call missing(of _all_);

set a;

_rc=h1.find(key:lowcase(strip(common_variable)));

output AB;

if _rc ne 0 then output AB_dummy;

drop _rc;

run;

🔒 This topic is solved and locked.