topic Re: Access formula to SAS in SAS Procedures

Access formula to SAS

jbear — Fri, 23 Jan 2015 17:14:56 GMT

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

Re: Access formula to SAS

ballardw — Fri, 23 Jan 2015 17:28:32 GMT

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

Re: Access formula to SAS

PGStats — Fri, 23 Jan 2015 17:57:43 GMT

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

Re: Access formula to SAS

jbear — Fri, 23 Jan 2015 18:22:02 GMT

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

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

Re: Access formula to SAS

PGStats — Fri, 23 Jan 2015 18:53:29 GMT

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;

Re: Access formula to SAS

jbear — Fri, 30 Jan 2015 21:33:23 GMT

Excellent...it worked.