Mod Function

[SASPhile]

case when mod(a.acct_no,100) eq 0 then b.cust_xref_id else a.a_amx end as basic_cust_xref_id

here the acct_no is character and its length is 13.

How to make this work?Mod doesnt work for characters

[Ksharp]

Change it into numeric type.

mod(input(a.acct_no,best32.),100)

[SASPhile]

How to avoid this when we give best32. format?

Invalid (or missing) arguments to the MOD function have caused the function to return a missing value.

[Astounding]

When you say the length of ACCT_NO is 13, does that mean that it is always either blank or 13 characters long?  If so, you could replace this:

mod(acct_no,100) eq 0

Use this instead:

substr(acct_no, 12, 2)='00'

If it is possible that ACCT_NO would contain a smaller number of digits, the expression becomes a bit more complicated:

substr(acct_no, max(1, lengthn(acct_no)-1), 2)='00'

There's no need to convert to numeric to see if it ends with '00'.

If it is possible that ACCT_NO would contain a mix of digits and other characters, you would have to spell out the desired result for that situation.

Good luck.

[art297]

You could use something like:

data have;

  infile cards truncover;

  informat acct_no $13.;

  input acct_no id amx;

  cards;

30  1 9

aaa 2 9

.   3 9

99  4 9

100 5 9

101 6 9

;

proc sql;

  create table want as

   select *,

      case when missing(acct_no) then .

        when missing(input(acct_no,? 32.)) then .

        when mod(input(acct_no,? 32.),100) then amx

        else id

      end as basic_cust_xref_id

    from have

  ;

quit;

[SASPhile]

data have;

  infile cards truncover;

  informat acct_no $13.;

  input acct_no id amx;

  cards;

30  1 9

aaa 2 9

20ab   3 9

99  4 9

100 5 9

101 6 9

;

In the above dataset, if a QC has to be done where we will create a separate dataset that contains only acct_no that have other characters other than digits?

[Reeza]

use the functions anydigit anyalpha to flag observations with letters to qc

[Astounding]

Here's a set of possible values for ACCT_NO.  Should all of them get flagged by a QC process, or just some of them?

ABC135

3

100.224

12-34-5678

123,456

123.000

456.

     12345000

You actually have the harder task here, deciding what the full set of rules should be.  The programming will be relatively brief.

[SASPhile]

Some

[PGStats]

Use

case when mod(coalesce(input(a.acct_no, ? best13.),0),100) eq 0 then b.cust_xref_id else a.a_amx end as basic_cust_xref_id

or

case when mod(coalesce(input(a.acct_no, ? best13.),1),100) eq 0 then b.cust_xref_id else a.a_amx end as basic_cust_xref_id

depending if you want the value b.cust_xref_id or a.a_amx when a.acct_no is not a proper number.

PG