Lowcase and Strip functions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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
Respected Advisor
Posts: 3,837

Re: Lowcase and Strip functions

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

View solution in original post


All Replies
Solution
‎08-18-2014 02:37 AM
Respected Advisor
Posts: 3,837

Re: Lowcase and Strip functions

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

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

Thanks for the reply RW9.

I have tried this option and it works perfectly.

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

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;

Respected Advisor
Posts: 3,837

Re: Lowcase and Strip functions

Under the assumption that the relationship between table A and table B is manySmiley Surprisedne 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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 237 views
  • 3 likes
  • 3 in conversation