BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jbear
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
ballardw
Super User

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

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
jbear
Calcite | Level 5

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

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
jbear
Calcite | Level 5

Excellent...it worked.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 863 views
  • 3 likes
  • 3 in conversation