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
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
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
Why not tell us what this ACCESS formula is meant to do instead of asking us to do all the decoding-recoding work? - PG
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
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
Excellent...it worked.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.