I'm doing a JOIN and have several columns showing '.' for the missing data values. If I set NODATA, then all of the missing values become that value.
I need one of the columns to show blanks and others show '0'. I know I can do it with HOLDing the data first and then manipulating the MISSING, but I was wandering if there is a technique to do it in one pass, without the HOLD.

Thank you in advance for any inputs!

The problem you have, I believe, is that internally, before output, these 'missing' values are actually treated as blank or zero. It is differentiating between a real blank or zero and a 'missing' field treated as blank or zero.

If a field could never be blank or zero, then you can test it against blank or zero in a COMPUTE and put in the value you want.

Does that make sense?

The thing is, Alan, there are no missing values in the original databases. It is after the JOIN, because I'm using SET ALL = ON, that the missing values appear.
I tried re-DEFINE-ing with IF against MISSING, but that did not work.

That's what I was trying, in my own way, to say.

Internally WF is treating the values as either blank or zero. Missing is not an option, the values are not MISSING as such, just 'not there', and you can only check against a blank for alpha or zero for numeric for these 'not there' values.

Problem comes when values are really blank or zero as opposed to not there, which causes the major issue.

Thank you Alan.
The fields are all numeric (and unfortunately I DO need one of them to be blank and not 0). I did try checking against 0s

FIELD2 = IF FIELD1 EQ 0 THEN ' ' ELSE FIELD1;

but it still did not work - it still showed '.'s.

Is there something I'm missing or it just cannot be done in one pass?

Thank you!

I can't really tell from your code what the formats of your fields are, but you don't have any format specified in the above code. Assuming FIELD1 IS numeric, try this:

DEFINE FILE filename
FIELD2/D12=IF FIELD1 EQ MISSING THEN 0 ELSE FIELD1;
FIELD3/D12S=IF FIELD1 EQ MISSING THEN 0 ELSE FIELD1;
END

If there is no joined-to value for field1, field2 should show up as 0, field3 should show up as blank (zero-supressed).

I don't think this can be done in one pass then. You are trying to put an alpha (blank) into a numeric.

Thank you, Darin, but that's the first thing I've tried and it did not work. It might be that I'll have to stick with the two pass (HOLD first) way.

Thank you everyone for their suggestions and inputs.

If someone does find/know a way, please tell, I'd be very much interested.

The problem you correctly identified. I think we just didn't go far enough. If you move these to a COMPUTE (post internal matrix) it will work. Here's an example:

JOIN EMPDATA.PIN IN EMPDATA TO ALL TRAINING.PIN IN TRAIN2 AS JOIN1
SET ALL=PASS
TABLE FILE EMPDATA
PRINT LASTNAME AND FIRSTNAME AND COURSECODE AND EXPENSES
COMPUTE FIELD2/D12=IF EXPENSES EQ MISSING THEN 0 ELSE EXPENSES;
COMPUTE FIELD3/D12S=IF EXPENSES EQ MISSING THEN 0 ELSE EXPENSES;
BY PIN
WHERE EXPENSES GT 3000
END

AWESOME!!!
Thank you very much, Darin! That worked! I just had to re-DEFINE one of the fields to ALPHA, so I can do EQ '' instead of EQ '0'.

Thank you for that and reminding me that COMPUTE is done POST!!! internal matrix!

Alan did mention COMPUTE, but I kinda got stuck on the DEFINEs. Sorry, Alan!

P.S. Darin, is there a way to give you a plus or something ? Sorry, I'm new to the Forums.