Solved
Contributor
Posts: 29

# Access formula to SAS

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

Accepted Solutions
Solution
‎01-23-2015 01:53 PM
Posts: 5,543

## Re: Access formula to SAS

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

PG

All Replies
Super User
Posts: 13,583

## Re: Access formula to SAS

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

Posts: 5,543

## Re: Access formula to SAS

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

PG
Contributor
Posts: 29

## Re: Access formula to SAS

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

Solution
‎01-23-2015 01:53 PM
Posts: 5,543

## Re: Access formula to SAS

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

PG
Contributor
Posts: 29

## Re: Access formula to SAS

Excellent...it worked.

🔒 This topic is solved and locked.