Hi all I am looking for help with a DFIX file that we re preparing for a feed to a third party application the reqirements are quite rigid that they want a pipe delimited file with no spaces. I chose DFIX because attempting to do concatenations gave me leading or triling spaces. the DFIX works quite well except in regards to null values. when I have empty fields I have spaces between the pipes. | | Anyone have have any tips or insight as to how i could remove these?

the code is faily complex so I will only show the last pass through the data. My hold files are default until thsi point which should be alpha.

-*************JOIN ALL TOGETHER AND ADD HONORS AND SPORT****************************************************************
JOIN CLEAR *
JOIN
LEFT_OUTER HOLDSUMMA2.HOLDSUMM.PIDM_KEY IN HOLDSUMMA2 TO UNIQUE
GOREMAL.GOREMAL.GOREMAL_PIDM IN GOREMAL TAG J20 AS J20
END
JOIN
LEFT_OUTER HOLDSUMMA2.HOLDSUMM.PIDM_KEY IN HOLDSUMMA2 TO UNIQUE
HOLDSPRT.HOLDSPRT.SGRSPRT_PIDM IN HOLDSPRT TAG J11 AS J11
END
JOIN
LEFT_OUTER HOLDSUMMA2.HOLDSUMM.PIDM_KEY IN HOLDSUMMA2 TO UNIQUE
HOLDHON.HOLDHON.RRRAREQ_PIDM IN HOLDHON TAG J13 AS J13
END
DEFINE FILE HOLDSUMMA2
BUILDING/A30=
IF HOLDSUMMA2.HOLDSUMM.DEF_BUILD EQ ' ' THEN 'Off Campus'
ELSE HOLDSUMMA2.HOLDSUMM.DEF_BUILD;
DUMMY/A4='N/A';
D_MONTH/M=DEFDOB;
DEF_MONTH_ALPHA/A2=EDIT(D_MONTH);
D_DAY/D=DEFDOB;
DEF_DAY_ALPHA/A2=EDIT(D_DAY);
D_YEAR/YY=DEFDOB;
DEF_YEAR_ALPHA/A4=EDIT(D_YEAR);
DEF_ALPHA_DATE/A10=DEF_YEAR_ALPHA || '-' ||DEF_MONTH_ALPHA || '-' ||DEF_DAY_ALPHA;
DEF_TODAY/YYMD=&YYMD;
D_MONTH_TODAY/M=DEF_TODAY;
DEF_MONTH_ALPHA1/A2=EDIT(D_MONTH_TODAY);
D_DAY_TODAY/D=DEF_TODAY;
DEF_DAY_ALPHA1/A2=EDIT(D_DAY_TODAY);
D_YEAR_TODAY/YY=DEF_TODAY;
DEF_YEAR_ALPHA1/A4=EDIT(D_YEAR_TODAY);
DEF_ALPHA_DATE1/A10=DEF_YEAR_ALPHA1 || '-' ||DEF_MONTH_ALPHA1 || '-' ||DEF_DAY_ALPHA1;
DEF_GEN/A6=DECODE HOLDSUMMA2.HOLDSUMM.GENDER( F Female M Male ELSE '' );
DEF_ATH/A12=IF J11.HOLDSPRT.SGRSPRT_ACTC_CODE EQ ' ' THEN 'Not Athlete' ELSE J11.HOLDSPRT.SGRSPRT_ACTC_CODE ;
DEF_HON/A24=IF J13.HOLDHON.RTVTREQ_SHORT_DESC EQ ' ' THEN 'Not Honors' ELSE J13.HOLDHON.RTVTREQ_SHORT_DESC ;
END
-*************FORMAT DISPLAY WITH HYPHENS IN DATES APPENS SYSDATE FOMRATTED WITH HYPHENS****************************************
TABLE FILE HOLDSUMMA2
PRINT
HOLDSUMMA2.HOLDSUMM.ID
HOLDSUMMA2.HOLDSUMM.GOBTPAC_EXTERNAL_USER
HOLDSUMMA2.HOLDSUMM.LASTN
HOLDSUMMA2.HOLDSUMM.FIRSTN
HOLDSUMMA2.HOLDSUMM.SPRIDEN_MI
HOLDSUMMA2.HOLDSUMM.DEF_ALPHA_DATE
HOLDSUMMA2.HOLDSUMM.DEF_GEN
HOLDSUMMA2.HOLDSUMM.ETHN_DESC
HOLDSUMMA2.HOLDSUMM.BUILDING
HOLDSUMMA2.HOLDSUMM.CK_BED_SPACE
HOLDSUMMA2.HOLDSUMM.ADDRESSCB
HOLDSUMMA2.HOLDSUMM.CITYCB
HOLDSUMMA2.HOLDSUMM.STATCB
HOLDSUMMA2.HOLDSUMM.ZIPCB
HOLDSUMMA2.HOLDSUMM.PHONELO
HOLDSUMMA2.HOLDSUMM.PHONECE
HOLDSUMMA2.HOLDSUMM.ADDRESSPR
HOLDSUMMA2.HOLDSUMM.CITYPR
HOLDSUMMA2.HOLDSUMM.STATPR
HOLDSUMMA2.HOLDSUMM.ZIPPR
HOLDSUMMA2.HOLDSUMM.PHONEPR
HOLDSUMMA2.HOLDSUMM.PHOMEM
J20.GOREMAL.GOREMAL_EMAIL_ADDRESS
HOLDSUMMA2.HOLDSUMM.ADVISOR
HOLDSUMMA2.HOLDSUMM.INST_PTGPA_GPA
HOLDSUMMA2.HOLDSUMM.INST_LGPA_GPA
DEF_ATH
DUMMY
DEF_HON
DUMMY
DEF_ALPHA_DATE1
BY HOLDSUMMA2.HOLDSUMM.ID NOPRINT
BY HIGHEST J20.GOREMAL.GOREMAL_ACTIVITY_DATE NOPRINT
WHERE ( J20.GOREMAL.GOREMAL_EMAL_CODE EQ 'UNIS' ) AND ( J20.GOREMAL.GOREMAL_STATUS_IND NE 'I' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS OUT1 FORMAT DFIX DELIMITER |
END

thanks

This message has been edited. Last edited by: Geoff Fish,

construct your entire record as one variable, pipes and all.
use STRIP command to make sure you haven't got anything you don't want.
FILEDEF your output as a .CSV file extension, and SAVE it, or HOLD FORMAT ALPHA

Have you tried these?

SET HNODATA = ''
SET HOLDMISS = ON/OFF

ho! so much easier

I gave it a spin to no avail

tried SET NODAT and SET HNODATA. Neither was effecive

Here's one more to try in conjunction with HNODATA and HOLDMISS:

SET NULL = ON

so I should use all 3?
I have aboout 6 hold tables leading up to this last pass through the data

So I should use

SET NULL = ON
SET HNODATA = ON
AND HOLDMISS = ON?

I ALSO TRIED SET NODATA = ,$

Yes, I suggest using the three together (with HOLDMISS=ON). If that doesn't work, the next step would be to ensure that all fields that may have missing values are defined with the MISSING=ON attribute (either in the master/synonym or in the DEFINE). Try it with a single field first. If it still doesn't work, then possible work-arounds might be susannah's suggestion, or a -READ/STRREP/-WRITE repeat loop following your DFIX HOLD to replace '| |' with '||'.

It may just be a case of defining the field correctly using MISSING ON instead of setting the value to '' -

DEFINE FILE CAR
  NEW_DCOST/D12 MISSING ON = IF COUNTRY NE 'ENGLAND' THEN DCOST ELSE MISSING;
END

TABLE FILE CAR
  SUM RCOST
      COMPUTE NEW_RCOST/D12 MISSING ON = IF COUNTRY EQ 'ENGLAND' THEN RCOST ELSE MISSING;
      COMPUTE NEW_TEXT/A3 = IF COUNTRY EQ 'ENGLAND' THEN 'ENG' ELSE '';
      NEW_DCOST
      DCOST
   BY COUNTRY
   BY CAR
   BY MODEL
ON TABLE HOLD AS MYDFIX FORMAT DFIX DELIMITER |
END
-RUN

CMD TYPE mydfix.ftm

Works with either PCHOLD or HOLD output.

T

As a backup plan , you can build the pipe delimited line in a define, etc, and use PUTDDREC to write the line to a file, with the 4th parameter (String length) set using ARGLEN to remove the trailing spaces.

I changed form the DFIX to tab and concatenated the record into one big field this solved my issue.

Never discount brute force.

DEFINE FILE HOLDALL
DEMORECORD/A1200=
HOLDALL.HOLDALL.ID || '|' ||
HOLDALL.HOLDALL.GOBTPAC_EXTERNAL_USER || '|' ||
HOLDALL.HOLDALL.LASTN || '|' ||
HOLDALL.HOLDALL.FIRSTN || '|' ||
HOLDALL.HOLDALL.SPRIDEN_MI || '|' ||
HOLDALL.HOLDALL.DEF_ALPHA_DATE || '|' ||
HOLDALL.HOLDALL.DEF_GEN || '|' ||
HOLDALL.HOLDALL.ETHN_DESC || '|' ||
HOLDALL.HOLDALL.BUILDING || '|' ||
HOLDALL.HOLDALL.CK_BED_SPACE || '|' ||
HOLDALL.HOLDALL.ADDRESSCB || '|' ||
HOLDALL.HOLDALL.CITYCB || '|' ||
HOLDALL.HOLDALL.STATCB || '|' ||
HOLDALL.HOLDALL.ZIPCB || '|' ||
HOLDALL.HOLDALL.PHONELO || '|' ||
HOLDALL.HOLDALL.PHONECE || '|' ||
HOLDALL.HOLDALL.ADDRESSPR || '|' ||
HOLDALL.HOLDALL.ADDRESSPR || '|' ||
HOLDALL.HOLDALL.CITYPR || '|' ||
HOLDALL.HOLDALL.STATPR || '|' ||
HOLDALL.HOLDALL.ZIPPR || '|' ||
HOLDALL.HOLDALL.PHONEPR || '|' ||
HOLDALL.HOLDALL.PHOMEM || '|' ||
HOLDALL.HOLDALL.GOREMAL_EMAIL_ADDRESS || '|' ||
HOLDALL.HOLDALL.CLAS_DESC || '|' ||
HOLDALL.HOLDALL.MAJR_DESC1 || '|' ||
HOLDALL.HOLDALL.ADVISOR || '|' ||
HOLDALL.HOLDALL.A_PTGPA || '|' ||
HOLDALL.HOLDALL.A_LGPA || '|' ||
HOLDALL.HOLDALL.DEF_ATH || '|' ||
HOLDALL.HOLDALL.DUMMY || '|' ||
HOLDALL.HOLDALL.DEF_HON || '|' ||
HOLDALL.HOLDALL.DUMMY || '|' ||
HOLDALL.HOLDALL.DEF_ALPHA_DATE1;
END