DEFINE: make column where each row has one day of current month

[kaeserea]

Hi!

I have incomplete data and wonder whether I can complete it with a DEFINE. The data is about sales of different products. I have a field named PRODUCT, one named NUMBER and a field named DATE. The DATE field has the format e.g. 20040112 for 12th January 2004.

But not every product is sold every day. So if e.g. a product named "soap" is not sold on 12th January 2004 there will be no row in the data. Yet I'd need a row where PRODUCT=soap, NUMER=0 and DATE=20040112.

What I now thought is why not generate this? I can have a list of all products sold in this month (namely in the field PRODUCT) and where one of these is not sold on one day in that month I insert a row with NUMER=0.

Does anybody have an idea how to do this?

Eva

 

[gizzy17]

I would recomend using the McGyver technique for this. You could start with a base date and add the counter to it.

eg. SALE_DATE/YYMD = SALE_DATE + COUNTER;

Check the following for more information on how this is done.

http://techsupport.informationbuilders.com/sps/81701058.html

 

[craigiep]

Eva,

create a file containing all of the dates you're interested in - you may have a calendar table that you can use, or use the method above

eg
DEFINE FILE CAR
START_DATE/YYMD = '20031231';
END

TABLE FILE CAR
PRINT COUNTRY NOPRINT
AND COMPUTE
COUNTER/I9 = LAST COUNTER + 1; NOPRINT
AND COMPUTE
SALEDATE/YYMD = START_DATE + COUNTER;
WHERE RECORLDIMIT EQ 31
ON TABLE SAVE AS CALDAYS
END
-* set up a loop counter
-SET &LOOP = &LINES - 1;

-* Then use this file to force an occurrence for each day

-* eg
DEFINE FILE MYFILE
-* I had to convert the date to alpha for this to work
CALDATE/A8YYMD = SALEDATE;
END

TABLE FILE MYFILE

PRINT PRODUCT
NUMBER
BY CALDATE ROWS

-* set up a loop to insert the dates required

-READ CALDAYS &CDATE.A8
'&CDATE.EVAL'

-REPEAT PRLOOP &LOOP TIMES;
-READ CALDAYS &CDATE.A8
OVER '&CDATE.EVAL'
-PRLOOP

ON TABLE HOLD AS SALES
END
-RUN

good luck !