Working with dates

[mo0926]

How does one eliminate zeros in an output of dobs?

For example: output all complete dobs such as 20051201... etc in a column and not partially incomplete dobs like 20050000

[rtaylor]

mauricexxvi,

That depends on what the input data type is and what you're trying to do with it. I have to assume that your output type is STRING, since you seem to want to get rid of trailing zeroes.

Here's one way to approach the issue:

Code: Select all

LoseTrailingZeroes(STRING8 dob) := FUNCTION
  STRING4 YY := dob[1..4];
  STRING2 DD := IF(dob[7..8]='00','',dob[7..8]);
  STRING2 MM := IF(dob[5..6]='00' AND DD='','',dob[5..6]);
  RETURN (STRING8)(YY + MM + DD);
END;

LoseTrailingZeroes('20050000'); //year only
LoseTrailingZeroes('20051000'); //year+month
LoseTrailingZeroes('20051020'); //full date

HTH,

Richard

[mo0926]

Thank you, Richard! In this case I am dealing with the header file where the date of birth is an integer. Does this change the solution?

[rtaylor]

mauricexxvi,

Yes, it does. If the date field is an integer type then you can't remove the trailing zeroes (doing that would change the numeric value of the field). You can only replace trailing zeroes with spaces in a STRING.

What's the real problem you're trying to solve with this? If it's just a display issue, then you can cast the date to a STRING8 then strip the trailing zeroes and display that.

But if you want them stored that way, then you'd have to change the data type in the file to a STRING8 and live with the doubled storage requirement (4 bytes times billions of records is a lot of disk space).

HTH,

Richard

[mo0926]

More of a display issue where I am trying to output the dob column without the incomplete dobs.

So taking column A and transforming it to column B below:

A. 1. 20051200 B.1. 200512
2. 19870000 2. 1987
3. 20011011 3. 20011011

[rtaylor]

mauricexxvi,

Here's the same function, changed to take the UNSIGNED4 date and return a STRING8 date with the trailing zeroes removed:

Code: Select all

LoseTrailingZeroes(UNSIGNED4 indob) := FUNCTION
  STRING8 dob := (STRING8)indob;
  STRING4 YY := dob[1..4];
  STRING2 DD := IF(dob[7..8]='00','',dob[7..8]);
  STRING2 MM := IF(dob[5..6]='00' AND DD='','',dob[5..6]);
  RETURN YY + MM + DD;
END;

LoseTrailingZeroes(20050000); //year only
LoseTrailingZeroes(20051000); //year+month
LoseTrailingZeroes(20051020); //full date
LoseTrailingZeroes(20050020); //year+day (invalid date)

HTH,

Richard

[mo0926]

Perfect! Your solution helped me arrive at this solution.

Code: Select all

LoseTrailingZeroes(Unsigned4 indob) := Function
  Unsigned8 dob := (Unsigned8)indob;
  String4 YY  := dob[1..4];
  String2 DD  := if(dob[7..8]='00','',dob[7..8]);
  String2 MM  := if(dob[5..6]='00' AND DD='','',dob[5..6]);
  Return (Unsigned8)(YY + MM + DD);
End;

FilteredDobs := $.New_Persons.File( Not dob = 0);

Rec := Record
    Lexid := Filtereddobs.lexid;
    Dob := Filtereddobs.dob;
    Src := Filtereddobs.src;
End;

Rec SlimRec( Filtereddobs Le ) := Transform
  Self.dob := LoseTrailingZeroes(le.dob);
  Self := Le;
End;

Shared RecSlim := Project(FilteredDobs, SlimRec(Left));

NonBlankDob := Record
  RecSlim.dob;
End;

c_dob := Table(RecSlim, Nonblankdob, dob);
dc_dob := Distribute(c_dob, Hash(dob));
sdc_dob := Sort(dc_dob, dob, Local);
Shared dsdc_dob := Dedup(sdc_dob, dob, Local);

crosstab_dob := Record
  RecSlim.dob;
  RecordCnt := Count(Group);
End;

Out_Crosstab_dob := Table(RecSlim, crosstab_dob, dob);
Export S_Crosstab_dob := Sort(Out_Crosstab_dob, -RecordCnt);

[rtaylor]

mauricexxvi,

This line of code:

Code: Select all

  Unsigned8 dob := (Unsigned8)indob;

should more properly be this:

Code: Select all

  STRING8 dob := (STRING8)indob;

because otherwise in the next three definitions the system has to implicitly cast your UNSIGNED8 dob to a STRING8 in order to do do the string slicing operation. Better to do a single explicit cast than make the system do it implicitly three times.

Also, this line:

Code: Select all

Return (Unsigned8)(YY + MM + DD);

is actually changing the value of the input date. IOW, you're not stripping trailing zeroes, you're changing the numeric 20050000 value to 2005. That's why my example returned a STRING8, NOT an UNSIGNED8 value.

HTH,

Richard

[mo0926]

Thank you, Richard. That helped clear some of the errors I was getting.

[Allan]

If a date is a number its trivial to extract components of a date.
Assuming dates are of the form YYYYMMDD

then:

Code: Select all

Year is just      date DIV 10000
Month is just     (date DIV 100) % 100
Day is just       date % 100


Also dates should be UNSIGNED not INTEGER.