I have this flat file and want to translate it to a more comprehencive database

C=CA
Y=02,0101,0329,0520,0701,0805,0902,1014,S1111,1225,1226
Y=03,0101,0418,0519,0701,0804,0901,1013,S1111,1225,1226
Y=04,0101,0409,0524,0701,0802,0906,1011,S1111,1227,1228
Y=05,0103,0325,0523,0701,0801,0905,1010,S1111,1226,1227
Y=06,0102,0414,0522,0703,0807,0904,1009,S1113,1225,1226
Y=07,0101,0406,0521,0702,0806,0903,1008,S1112,1225,1226
Y=08,0101,0218,0321,0519,0701,0804,0901,1013,S1111,1225,1226
Y=09,0101,0216,0410,0518,0701,0803,0907,1012,S1111,1225,1228
Y=10,0101,0215,0402,0524,0701,0802,0906,1011,S1111,1227,1228
Y=11,0103,0221,0422,0523,0701,0801,0905,1010,S1111,1226,1227
C=US
Y=02,0101,0121,0218,0329,0527,0704,0902,S1014,S1111,1128,1225
Y=03,0101,0120,0217,0418,0526,0704,0901,S1013,S1111,1127,1225
Y=04,0101,0119,0216,0409,0531,0705,0906,S1011,S1111,1125,1224
Y=05,0117,0221,0325,0530,0704,0905,S1010,S1111,1124,1226
Y=06,0102,0116,0220,0414,0529,0704,0904,S1009,S1111,1123,1225
Y=07,0101,0115,0219,0406,0528,0704,0903,S1008,S1112,1122,1225
Y=08,0101,0121,0218,0321,0526,0704,0901,S1013,S1111,1127,1225
Y=09,0101,0119,0216,0410,0525,0703,0704,0907,S1012,S1111,1126,1225
Y=10,0101,0118,0215,0402,0531,0705,0906,S1011,S1111,1125,1224
Y=11,0117,0221,0422,0530,0704,0905,S1010,S1111,1124,1226
C=PQ
Y=02,0101,0329,0520,0624,0701,0805,0902,1014,1225,1226
Y=03,0101,0418,0519,0624,0701,0901,1013,1225,1226
Y=04,0101,0409,0524,0624,0701,0906,1011,1227,1228
Y=05,0103,0325,0523,0701,0801,0905,1010,1226,1227
Y=06,0102,0414,0522,0703,0807,0904,1009,S1113,1225,1226
Y=07,0101,0406,0521,0702,0806,0903,1008,S1112,1225,1226
Y=08,0101,0218,0321,0519,0701,0804,0901,1013,S1111,1225,1226
Y=09,0101,0216,0410,0518,0701,0803,0907,1012,S1111,1225,1228
Y=10,0101,0104,0402,0524,0624,0701,0906,1011,S1111,1227,1228
Y=11,0103,0104,0422,0523,0624,0701,0905,1010,S1111,1226,1227
C=BE
Y=02,0101,0329,0401,0501,1225,1226
Y=03,0101,0418,0421,0501,1225,1226
Y=04,0101,0409,0412
Y=05,0101,0325,0328,0501,1225,1226
Y=06,0101,0414,0417,0501,1225,1226
Y=07,0101,0406,0409,0501,1225,1226
Y=08,0101,0321,0324,0501,1225,1226
Y=09,0101,0410,0413,0501,1225
Y=10,0101,0402,0405
Y=11,0422,0425,1226
C=NL
Y=02,0101,0329,0401,0501,1225,1226
Y=03,0101,0418,0421,0501,1225,1226
Y=04,0101,0409,0412,0430,0520,0531
Y=05,0101,0325,0328,0501,1225,1226
Y=06,0101,0414,0417,0501,1225,1226
Y=07,0101,0406,0409,0501,1225,1226
Y=08,0101,0321,0324,0501,1225,1226
Y=09,0101,0410,0413,0501,1225
Y=10,0101,0402,0405
Y=11,0422,0425,1226

As you may see the code C=CA is the indication of the country
Y=10 will be the year 2010
And the rest are the non working days for that country.

What I need is a database with a field country and the nonworking day
somthing like this

COUNTRY   NONWORKDAY
NL        20100101
NL        20100402
NL        20100405
etc

I am sure somebody has a solution ready to use...

Hmmmm...

quote:

S1111

What are these?

Francis

To be honest, I have no idea...
Did not see it till now.
It might be bank holidays or maybe an indication it is just a half day, but I am sure it is an indicator, so it should be an extra field in the dataset.

I will ask however the application guru....maybe he has an idea.

For the US, I don't know what the S stands for, but,

for October - Columbus Day
for November - Veteran's Day

FYI

Here is a Master that takes care of everything, ONLY if there is no S in the data - because the Master uses OCCURS, each entry in the OCCURS must be the same length - 5.

FILE=HOLIDAY1, SUFFIX=FIX, DATASET='test/holiday1.txt', $

SEGNAME=HOLIDAY1, SEGTYPE=S0, $

FIELDNAME=DATA_KEY, ALIAS=DATA_KEY, USAGE=A02, ACTUAL=A02, $
FIELDNAME=DATA_VAL1, ALIAS=DATA_VAL1, USAGE=A02, ACTUAL=A02, $

FIELDNAME=DATA_VAL2, ALIAS=DATA_VAL2, USAGE=A200, ACTUAL=A200, $

DEFINE JURISDICTION/A02 = IF DATA_KEY EQ 'C=' THEN DATA_VAL1;
DEFINE DT_Y/A02 = IF DATA_KEY LIKE 'Y=' THEN DATA_VAL1 ELSE '';

SEGNAME=DATA_VAL2, PARENT=HOLIDAY1, POSITION=DATA_VAL2,  OCCURS=VARIABLE, $

FIELDNAME=DATA_SEP , ALIAS=DATA_SEP , USAGE=A01, ACTUAL=A01, $
FIELDNAME=DT_MD , ALIAS=DT_MD , USAGE=A04, ACTUAL=A04, $

DEFINE DT_YYMD/A08 = '20' | DT_Y | DT_MD;

Fex:

TABLE FILE HOLIDAY1
PRINT
JURISDICTION
DT_YYMD
END
-RUN

Perhaps someone else can figure out how to incorporate those pesky S dates.

This message has been edited. Last edited by: Francis Mariani,

There's this: [solved] reading variable field length and variable occurs

Yes Francis
I was aware of that solution of course, but this was a bit different since it has a comma separations. Although now I think it can be done with that.
You solution hoever brought me something I had never thought of.

this line of code

DEFINE JURISDICTION/A02 = IF DATA_KEY EQ 'C=' THEN DATA_VAL1;

It does not hold an "ELSE" firts thought that was a mistake, but now I do realise it is not! Experts and mistakes....

Thanks for the effort

DEFINE JURISDICTION/A02 = IF DATA_KEY EQ 'C=' THEN DATA_VAL1;

The no ELSE statement was intentional, but I'm not sure if it is proper coding - it does work though.

OK, I think this works.

MAS

FILE=HOLIDAY2, SUFFIX=FIX, DATASET='test/holiday2.txt', $

SEGNAME=HOLIDAY2, SEGTYPE=S0, $

FIELDNAME=DATA_KEY, ALIAS=DATA_KEY, USAGE=A02, ACTUAL=A02, $
FIELDNAME=DATA_VAL1, ALIAS=DATA_VAL1, USAGE=A02, ACTUAL=A02, $

FIELDNAME=DATA_VAL2, ALIAS=DATA_VAL2, USAGE=A200, ACTUAL=A200, $

DEFINE JURISDICTION/A02 = IF DATA_KEY EQ 'C=' THEN DATA_VAL1;
DEFINE DT_Y/A02 = IF DATA_KEY LIKE 'Y=' THEN DATA_VAL1 ELSE '';

FEX

SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE HOLIDAY2
PRINT
JURISDICTION
DT_Y
-*-- Add blank character to compensate for some dates with 'S' flag
COMPUTE DATA_VAL2X/A300 = STRREP (200, DATA_VAL2,  1, ',',   2, ', ', 300, 'A300'); NOPRINT
-*-- Remove newly added blank for dates with 'S' flag
COMPUTE DATA_VAL2Y/A300 = STRREP (200, DATA_VAL2X, 3, ', S', 2, ',S', 300, 'A300');

ON TABLE HOLD AS HOLIDAYH001 FORMAT ALPHA
END
-RUN

FILEDEF MASTER DISK holidayh001.mas
-RUN

-WRITE MASTER FILE=HOLIDAYH001, SUFFIX=FIX, $

-WRITE MASTER SEGNAME=HOLIDAYH001, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=JURISDICTION, ALIAS=JURISDICTION, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=DT_Y, ALIAS=DT_Y, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=DATA_VAL2, ALIAS=DATA_VAL2, USAGE=A300, ACTUAL=A300, $

-WRITE MASTER SEGNAME=DATA_VAL2, PARENT=HOLIDAYH001, POSITION=DATA_VAL2,  OCCURS=VARIABLE, $
-WRITE MASTER FIELDNAME=DT_SEP  , ALIAS=DT_SEP  , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=DT_FLAG , ALIAS=DT_FLAG , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=DT_MD , ALIAS=DT_MD , USAGE=A04, ACTUAL=A04, $

-WRITE MASTER DEFINE DT_YYMD/A09 = '20' | DT_Y | DT_MD;

TABLE FILE HOLIDAYH001
PRINT
JURISDICTION
DT_FLAG
DT_YYMD
-*-- Exclude rows that were for jurisdiction only
WHERE DT_Y NE '';
-*-- Exclude blank date occurs
WHERE DT_MD NE '';
END
-RUN

Another alternative

 

Master 
FILE=HOLIDAY, SUFFIX=FIX, DATASET='ibisamp/holiday.txt', $
 SEGNAME=HOLIDAY, SEGTYPE=S0, $
  FIELD = RECTYPE, ALIAS = C, USAGE = A1, ACTUAL = A1,$
   FIELDNAME=F1, ALIAS=F1, USAGE=A01, ACTUAL=A01, $
   FIELDNAME=JURISDICTION, ALIAS=JURISDICTION, USAGE=A02, ACTUAL=A02, $
 SEGNAME=YEAR, PARENT=HOLIDAY, $
  FIELD = RECTYPE, ALIAS = Y, USAGE = A1, ACTUAL = A1,$
  FIELDNAME=F2, ALIAS=F2, USAGE=A01, ACTUAL=A01, $
  FIELDNAME=YEAR, ALIAS=YEAR, USAGE=A02, ACTUAL=A2, $
  FIELDNAME=DATA_VAL2, ALIAS=DATA_VAL2, USAGE=A200, ACTUAL=A200, $
DEFINE DT_YYMD/A4 = '20' | YEAR  ;,$
 
Focexec
TABLE FILE HOLIDAY
PRINT
     DATA_VAL2
 COMPUTE TERIMINATOR/A1=' ';
BY JURISDICTION
BY DT_YYMD
ON TABLE HOLD FORMAT DFIX DELIMITER ','
END
-RUN
FILEDEF HOLDM DISK HOLD.MAS 
-RUN
-WRITE HOLDM  FILENAME=HOLD    , SUFFIX=DFIX, $
-WRITE HOLDM   SEGMENT=HOLD, SEGTYPE=S0, $
-WRITE HOLDM  FIELDNAME=JURISDICTION, ALIAS=JURISDICTION, USAGE=A2, ACTUAL=A2, $
-WRITE HOLDM  FIELDNAME=DT_YYMD,  USAGE=A4, ACTUAL=A4, $
-WRITE HOLDM  FIELDNAME=DUMMY,    USAGE=A1, ACTUAL=A1, $
-WRITE HOLDM  FIELDNAME=HOL1,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL2,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL3,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL4,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL5,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL6,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL7,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL8,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL9,  ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL10, ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL11, ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL12, ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL13, ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL14, ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  FIELDNAME=HOL15, ALIAS= , USAGE=A5, ACTUAL=A5, $
-WRITE HOLDM  DEFINE DT_HOL1/A8  MISSING ON= IF  EDIT(HOL1,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL1  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL1,'$9999')  ELSE  DT_YYMD | EDIT(HOL1,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL2/A8  MISSING ON= IF  EDIT(HOL2,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL2  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL2,'$9999')  ELSE  DT_YYMD | EDIT(HOL2,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL3/A8  MISSING ON= IF  EDIT(HOL3,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL3  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL3,'$9999')  ELSE  DT_YYMD | EDIT(HOL3,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL4/A8  MISSING ON= IF  EDIT(HOL4,'9')  EQ '' THEN MISSING
-WRITE HOLDM   ELSE IF HOL4  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL4,'$9999')  ELSE  DT_YYMD | EDIT(HOL4,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL5/A8  MISSING ON= IF  EDIT(HOL5,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL5  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL5,'$9999')  ELSE  DT_YYMD | EDIT(HOL5,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL6/A8  MISSING ON= IF  EDIT(HOL6,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL6  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL6,'$9999')  ELSE  DT_YYMD | EDIT(HOL6,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL7/A8  MISSING ON= IF  EDIT(HOL7,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL7  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL7,'$9999')  ELSE  DT_YYMD | EDIT(HOL7,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL8/A8  MISSING ON= IF  EDIT(HOL8,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL8  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL8,'$9999')  ELSE  DT_YYMD | EDIT(HOL8,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL9/A8  MISSING ON= IF  EDIT(HOL9,'9')  EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL9  CONTAINS 'S' THEN DT_YYMD | EDIT(HOL9,'$9999')  ELSE  DT_YYMD | EDIT(HOL9,'9999')  ;,$
-WRITE HOLDM  DEFINE DT_HOL10/A8 MISSING ON =IF  EDIT(HOL10,'9') EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL10 CONTAINS 'S' THEN DT_YYMD | EDIT(HOL10,'$9999') ELSE  DT_YYMD | EDIT(HOL10,'9999') ;,$
-WRITE HOLDM  DEFINE DT_HOL11/A8 MISSING ON =IF  EDIT(HOL11,'9') EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL11 CONTAINS 'S' THEN DT_YYMD | EDIT(HOL11,'$9999') ELSE  DT_YYMD | EDIT(HOL11,'9999') ;,$
-WRITE HOLDM  DEFINE DT_HOL12/A8 MISSING ON =IF  EDIT(HOL12,'9') EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL12 CONTAINS 'S' THEN DT_YYMD | EDIT(HOL12,'$9999') ELSE  DT_YYMD | EDIT(HOL12,'9999') ;,$
-WRITE HOLDM  DEFINE DT_HOL13/A8 MISSING ON =IF  EDIT(HOL13,'9') EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL13 CONTAINS 'S' THEN DT_YYMD | EDIT(HOL13,'$9999') ELSE  DT_YYMD | EDIT(HOL13,'9999') ;,$
-WRITE HOLDM  DEFINE DT_HOL14/A8 MISSING ON =IF  EDIT(HOL14,'9') EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL14 CONTAINS 'S' THEN DT_YYMD | EDIT(HOL14,'$9999') ELSE  DT_YYMD | EDIT(HOL14,'9999') ;,$
-WRITE HOLDM  DEFINE DT_HOL15/A8 MISSING ON =IF  EDIT(HOL15,'9') EQ '' THEN MISSING 
-WRITE HOLDM  ELSE IF HOL15 CONTAINS 'S' THEN DT_YYMD | EDIT(HOL15,'$9999') ELSE  DT_YYMD | EDIT(HOL15,'9999') ;,$
-RUN
TABLE FILE HOLD
PRINT 
 DT_HOL1
 DT_HOL2
 DT_HOL3
 DT_HOL4
 DT_HOL5
 DT_HOL6
 DT_HOL7
 DT_HOL8
 DT_HOL9
 DT_HOL10
 DT_HOL11
 DT_HOL12
 DT_HOL13
 DT_HOL14
 DT_HOL15
BY JURISDICTION
BY DT_YYMD
ON TABLE HOLD AS ALLHOLS FORMAT ALPHA
END
-RUN 
FILEDEF ALLHOLSM DISK ALLHOLS.MAS
-RUN
-WRITE ALLHOLSM  FILENAME=ALLHOLS    , SUFFIX=FIX, $
-WRITE ALLHOLSM  SEGMENT=ALLHOLS, SEGTYPE=S0, $
-WRITE ALLHOLSM  FIELDNAME=JURISDICTION, ALIAS=JURISDICTION, USAGE=A2, ACTUAL=A2, $
-WRITE ALLHOLSM  FIELDNAME=DT_YYMD,  USAGE=A4, ACTUAL=A4, $
-WRITE ALLHOLSM  FIELDNAME=HOLIDAYS,    USAGE=A120, ACTUAL=A120, $
-WRITE ALLHOLSM  SEGMENT=HOLS, SEGTYPE=S0, PARENT=ALLHOLS, POSITION=HOLIDAYS,OCCURS=15,$
-WRITE ALLHOLSM  FIELDNAME=HOLIDAY,    USAGE=A8, ACTUAL=A8, $
 
TABLE FILE ALLHOLS 
PRINT HOLIDAY
BY JURISDICTION
WHERE HOLIDAY NE '.'
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE NOPAGE
ON TABLE SET LINES 99999
END
 
 
 
 
 

This approach uses an old technique of identifying and counting every character in the holiday text file by use of the ALIAS keyword 'ORDER'. The first DEFINE/TABLE FILE extracts the countries and years, eliminates commas and leading 'S's, and breaks the remaining date characters into groups of four. The final DEFINE/TABLE FILE reassembles the date digits with the year and generates the final output.

APP FILEDEF MASTER DISK holidata.mas
APP FILEDEF HOLIDATA DISK ibisamp/holiday.txt
-RUN
-*
-WRITE MASTER FILE=HOLIDATA ,SUFFIX=FIX ,$
-WRITE MASTER SEGNAME=ONE ,SEGTYPE=S1 ,$
-WRITE MASTER FIELDNAME=DATAKEY  ,ALIAS=DATAKEY  ,USAGE=A4 ,ACTUAL=A4 ,$
-WRITE MASTER SEGNAME=TWO ,PARENT=ONE ,POSITION=DATAKEY ,OCCURS=VARIABLE ,$
-WRITE MASTER FIELDNAME=DATACHAR ,ALIAS=DATACHAR ,USAGE=A1 ,ACTUAL=A1 ,$
-WRITE MASTER FIELDNAME=CHARCNT  ,ALIAS=ORDER    ,USAGE=I4 ,ACTUAL=I4 ,$
-RUN
-*
DEFINE FILE HOLIDATA
 COUNTRY/A2   = IF EDIT(DATAKEY,'9$$$') EQ 'C' THEN EDIT(DATAKEY,'$$99') ELSE LAST COUNTRY ;
 YEAR/A4      = IF EDIT(DATAKEY,'9$$$') EQ 'Y' THEN ('20' | EDIT(DATAKEY,'$$99')) ELSE LAST YEAR ;
 SEPARATOR/A1 = IF DATACHAR EQ ',' OR 'S' THEN 'Y' ELSE 'N' ;
 COLCNT/I4    = IF (CHARCNT LT 6) THEN 0
           ELSE IF (CHARCNT EQ 6) THEN 1
           ELSE IF (SEPARATOR EQ 'Y') THEN LAST COLCNT
           ELSE IF (LAST COLCNT EQ 4) THEN 1
           ELSE (LAST COLCNT + 1);
 ROWCNT/I4    = IF (COLCNT EQ 1) THEN (ROWCNT + 1) ELSE LAST ROWCNT ;
END
-*
TABLE FILE HOLIDATA
 SUM DATACHAR
 BY COUNTRY
 BY YEAR
 BY ROWCNT NOPRINT
 ACROSS COLCNT
 WHERE SEPARATOR EQ 'N';
 WHERE CHARCNT GE 6 ;
 ON TABLE SET ASNAMES ON
 ON TABLE HOLD AS HOLDDATA
END
-*
DEFINE FILE HOLDDATA
 HOLIDATES/A8MDYY = DAT1 | DAT2 | DAT3 | DAT4 | YEAR ;
 HOLIDAYS/YYMD = HOLIDATES ;
END
-*
TABLE FILE HOLDDATA
 PRINT HOLIDAYS
 BY COUNTRY
 BY YEAR
 ON TABLE SET BYDISPLAY ON
END