Desktop productivity for business analysts and programmers

How to move a SPACE to a numeric field

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

How to move a SPACE to a numeric field

I have data with an '.' in a numeric field.  I want to move a space to this field. I would like to do this in Enterprise Guide. I have tried variations of this syntax:

case when t1.AF > 0 then PUT(t1.AF) =  ''
end

 

Input

AF AM BF
. . 9
. . .
. . 2

 Output

AF AM BF
    9
     
    2

Accepted Solutions
Solution
‎01-04-2017 09:44 AM
Super User
Super User
Posts: 6,318

Re: How to move a SPACE to a numeric field

Just change the system option to print a space instead of a period for missing numeric values.

options missing=' ';

If you do want to recode your variable to character then your CASE statement would look like this:

case
  when not missing(t1.AF) then PUT(t1.AF,best12.)
  else ' '
end

You could pick a different format to use in the PUT() function depending on how you want the values displayed.

View solution in original post


All Replies
Respected Advisor
Posts: 3,775

Re: How to move a SPACE to a numeric field

options missing=' ';

Contributor
Posts: 41

Re: How to move a SPACE to a numeric field

How does this syntax work in Enterprise Guide in build and advanced expression if my field name is AF?

 

 

Grand Advisor
Posts: 10,210

Re: How to move a SPACE to a numeric field

NO "expression" needed. The period is indicating that the value is missing. The option sets the display to show missing with a different character, a blank. The "condition" to create a character variable with a blank in a CASE statement would be

 

It looks like you may be missing an ELSE portion of your CASE statement: Else " " as BF. But you didn't show the entire case statement as the result variable isn't indicated.

Contributor
Posts: 41

Re: How to move a SPACE to a numeric field

My field is numeric so my else cannot be "" for a blank.  I do not want a zero.  Ideas?

Solution
‎01-04-2017 09:44 AM
Super User
Super User
Posts: 6,318

Re: How to move a SPACE to a numeric field

Just change the system option to print a space instead of a period for missing numeric values.

options missing=' ';

If you do want to recode your variable to character then your CASE statement would look like this:

case
  when not missing(t1.AF) then PUT(t1.AF,best12.)
  else ' '
end

You could pick a different format to use in the PUT() function depending on how you want the values displayed.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 315 views
  • 4 likes
  • 4 in conversation