Help using Base SAS procedures

Access formula to SAS

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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_numalt_numnew_num (outcome wanted)
6733708564/6782120000000002

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
Respected Advisor
Posts: 4,926

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

View solution in original post


All Replies
Super User
Posts: 11,343

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

Respected Advisor
Posts: 4,926

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
Respected Advisor
Posts: 4,926

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 235 views
  • 3 likes
  • 3 in conversation