topic Re: mimic oracle's S099999 format in SAS Programming

mimic oracle's S099999 format

ChrisNZ — Wed, 14 Sep 2011 22:58:39 GMT

Hi,

I am creating a fixed-column flat file that requires number to be written as

+0000001234

-000000000000001234

The width varies.

I tried creating a picture format using fill= and prefix= but couldn't create a single format that I could use like:

put number orasigned6.; to write +01234

and

put number orasigned8.; to write +0001234

I had to create format for each length.

Anyone smarter (or rather more knowledgeable ;o) than me?

Thank you.

mimic oracle's S099999 format

FriedEgg — Wed, 14 Sep 2011 23:53:01 GMT

I don't think there is a way to do it with proc format to have a properly filled dynamic length, although there should be, probably beyond my knowledge as well.

data _null_;

input nbr;

if nbr<0 then sign='-'; else sign='+';

want6=sign || strip(put(abs(nbr),Z6.));

want8=sign || strip(put(abs(nbr),Z8.));

put want6= want8=;

cards;

1234

-1234

;

run;

want6=+001234 want8=+00001234

want6=-001234 want8=-00001234

Re: mimic oracle's S099999 format

ChrisNZ — Thu, 15 Sep 2011 01:06:33 GMT

I have dozens of these, so I'd rather write

put num1 orasigned6. num2 orasigned12. num3 orasigned8.

than run a full logic for each value.

Also, picture seems to have a silly limit of 16 digits. How dumb is that?

picture ora low-<0 ='999999999999999999' (prefix='-')

0-high ='999999999999999999' (fill='0' prefix='+') ;

ERROR: Number of digit selectors (18) cannot exceed 16.

Re: mimic oracle's S099999 format

ChrisNZ — Thu, 15 Sep 2011 03:17:05 GMT

Alright, I've resolved to write a function. Not the cleanest since a length=1 yields an access violation.

Still curious to know whether a format could do the trick though.

options cmplib=work.funcs;

proc fcmp outlib=work.funcs.temp;

function orasigned(ARG, LENGTH, MISSINGVAL $) $32;

if missing(ARG) then return(MISSINGVAL);

if LENGTH < 2 or LENGTH > 32 then do;

put 'ERROR in function orasigned: LENGTH should be between 2 and 32.';

return;

end;

return(ifc(ARG>=0,'+','-')||putn(abs(ARG),cats('z',LENGTH-1,'.0')));

endsub;

run;

mimic oracle's S099999 format

FriedEgg — Thu, 15 Sep 2011 03:40:52 GMT

I was just right in the middle of the pretty much the same thing...

proc fcmp outlib=work.funcs.temp;

function oras(v,l) $;

s=ifc(v<0,'-','+');

n=putn(v,cats('Z',l,'.'));

w=strip(s)||strip(n);

return (w);

endsub;

run;

options cmplib=work.funcs;

data _null_;

nbr=1234;

l=6;

want=oras(nbr,l);

put want=;

run;

want=+001234

Re: mimic oracle's S099999 format

ChrisNZ — Thu, 15 Sep 2011 05:05:43 GMT

Even that function is no good.

It will only return the full string length, so I have to trim the returned values as I concatenate them.

Not good at all. It looks like the good old macro language will have to be called upon, as usual.

If anyone has a better idea...

Message was edited by: Christian Graffeuille

mimic oracle's S099999 format

FriedEgg — Thu, 15 Sep 2011 14:39:32 GMT

I do not see an issue with the function approach, could you please provide sample of issue you see.

mimic oracle's S099999 format

Tom — Thu, 15 Sep 2011 18:48:24 GMT

Do you have SAS 9.3? I think you can use FCMP to create a format.

Re: mimic oracle's S099999 format

Tom — Thu, 15 Sep 2011 19:38:02 GMT

34 %macro s09(var,len);

35 %if ^%length(&len) %then %let len=12;

36 ifc(&var<0,put(&var,Z&len..),'+'||put(&var,Z%eval(&len-1).))

37 %mend s09;

39 data _null_;

40 input x ;

41 y=%s09(x,6);

42 z=%s09(x);

43 put (x y z) (=);

44 cards;

x=56 y=+00056 z=+00000000056

x=-23 y=-00023 z=-00000000023

x=0 y=+00000 z=+00000000000

x=. y=. z=.

mimic oracle's S099999 format

FriedEgg — Thu, 15 Sep 2011 19:43:45 GMT

Thanks, Tom. I would still like to know what issue Chris is having with the functional approach?

Re: mimic oracle's S099999 format

ChrisNZ — Thu, 15 Sep 2011 21:43:41 GMT

>I do not see an issue with the function approach.

It returns a full-length padded string which has to be trimmed.

>Do you have SAS 9.3?

Haha, good one! We are trying to migrate to 9.2.

Same here. Nothing beats macros eh?

%macro orasign(arg, len, miss);
  %if &len<3 %then %let len=3;
  %else %if &len>32 %then %let len=32;
  ifc(missing(&arg), &miss
 ,ifc(&arg>=0      , '+'||put(&arg,z%eval(&len-1).0)
 ,                   put(&arg,z&len..0)))
%mend;
 
data _null_;
  input A ;
  B=%orasign(A, 19, '-999999999999999999');
  put '/' B '\';
cards;       
23.1
 0
-23
.
run;

The downside of course is the length of the whole expression. If hundreds of variables formatted this way are concatenated, one could reach the limit of a datastep statement length.