BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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

9 REPLIES 9
Ksharp
Super User

Change it into numeric type.

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

SASPhile
Quartz | Level 8

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
PROC Star

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
Opal | Level 21

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
Quartz | Level 8

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
Super User

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

Astounding
PROC Star

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
Quartz | Level 8

Some

PGStats
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2002 views
  • 3 likes
  • 6 in conversation