topic Re: Mod Function in SAS Programming

Mod Function

SASPhile — Wed, 28 Nov 2012 04:59:30 GMT

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

Re: Mod Function

Ksharp — Wed, 28 Nov 2012 06:36:59 GMT

Change it into numeric type.

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

Re: Mod Function

SASPhile — Wed, 28 Nov 2012 16:23:46 GMT

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.

Re: Mod Function

Astounding — Wed, 28 Nov 2012 16:34:21 GMT

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.

Re: Mod Function

art297 — Wed, 28 Nov 2012 18:01:16 GMT

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;

Re: Mod Function

SASPhile — Thu, 29 Nov 2012 15:59:19 GMT

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?

Re: Mod Function

Reeza — Thu, 29 Nov 2012 16:01:16 GMT

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

Re: Mod Function

Astounding — Thu, 29 Nov 2012 16:12:30 GMT

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

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.

Re: Mod Function

SASPhile — Thu, 29 Nov 2012 19:10:34 GMT

Some

Re: Mod Function

PGStats — Thu, 29 Nov 2012 19:41:19 GMT

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

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.