DATA Step, Macro, Functions and more

mimic oracle's S099999 format

Reply
PROC Star
Posts: 1,555

mimic oracle's S099999 format

Hi,

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

+0000001234

or

-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.

Trusted Advisor
Posts: 1,300

mimic oracle's S099999 format

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

PROC Star
Posts: 1,555

Re: mimic oracle's S099999 format

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.

PROC Star
Posts: 1,555

Re: mimic oracle's S099999 format

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;

Trusted Advisor
Posts: 1,300

mimic oracle's S099999 format

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

PROC Star
Posts: 1,555

Re: mimic oracle's S099999 format

Even that function is no good. Smiley Sad

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

Trusted Advisor
Posts: 1,300

mimic oracle's S099999 format

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

Super User
Super User
Posts: 6,495

mimic oracle's S099999 format

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

Super User
Super User
Posts: 6,495

Re: mimic oracle's S099999 format

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;

38

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=.

Trusted Advisor
Posts: 1,300

mimic oracle's S099999 format

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

PROC Star
Posts: 1,555

Re: mimic oracle's S099999 format

>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.

Ask a Question
Discussion stats
  • 10 replies
  • 172 views
  • 0 likes
  • 3 in conversation