Access formula to SAS

[jbear]

Hello,

I need some assistance in translating this MS Access formula to SAS code:

Format(IIf([contract_num] Is Not Null And [contract_num] Not Like '*[!0-9]*',Left(Replace(Replace(Replace(Trim([contract_num]),"/",""),"-","")," ",""),10),Left(Replace(Replace(Replace(Trim([loan_num]),"/",""),"-","")," ",""),10)),"0000000000")

My SAS code is missing some cases in the false argument of the IIf statement:

Example case missed:

contract_num	alt_num	new_num (outcome wanted)
6733708564/67821	2	0000000002

Current SAS code:

  /**/
data want;
        set have;
                if not missing(contract_num) and countc(contract_num,'0123456789') > 0 then  new_num = substrn(compress(trim(contract_num),"/-"),1,10);
                     else new_num /*10 character length*/ = substrn(compress(trim(alt_num),"/-"),1,10);
run;

Thanks

[ballardw]

Maybe

else new_num /*10 character length*/ = put(input(alt_num,best8.),z10.0);

If Alt_num is character, or

else new_num /*10 character length*/ = put(alt_num,z10.0);

if alt_num is numeric

[PGStats]

Why not tell us what this ACCESS formula is meant to do instead of asking us to do all the decoding-recoding work? - PG

[jbear]

Hi PGStats,

I wasn't given too much info, but I interpreted the formula like this:

(a) if conditional: Not Null And [contract_num] Not Like '*[!0-9]*' ----> not null and any character outside 0-9 range is rejected, jumps to false argument

(b) replace statements: stripping some characters, I think the compress deals with this one

(c) the new_num is required to be padded with leading zeroes if len less than 10

I think that is what it is trying to due...and the false argument uses the alt_num since the condition is based on the contract_num.

I hope this helps

[PGStats · Accepted solution]

Try this then,

data have;

input (contract alt) (:$20.) ;

datalines;

6733708564/67821 2 0000000002

;

data want;

set have;

if missing(contract) or notdigit(trim(contract)) > 0 then

    new = put(input(compress(alt, "-/ "), best.), z10.0);

else new = put(input(compress(contract, "-/ "), best.), z10.0);

run;

proc print; run;

PG

[jbear]

Excellent...it worked.