Tue Feb 25, 2020 2:04 am

## Working with dates

Questions around writing code and queries
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
mo0926

Posts: 7
Joined: Thu Jan 09, 2020 3:57 pm

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 onlyLoseTrailingZeroes('20051000'); //year+monthLoseTrailingZeroes('20051020'); //full date`

HTH,

Richard
rtaylor

Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

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?
mo0926

Posts: 7
Joined: Thu Jan 09, 2020 3:57 pm

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
rtaylor

Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

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
mo0926

Posts: 7
Joined: Thu Jan 09, 2020 3:57 pm

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 onlyLoseTrailingZeroes(20051000); //year+monthLoseTrailingZeroes(20051020); //full dateLoseTrailingZeroes(20050020); //year+day (invalid date)`

HTH,

Richard
rtaylor

Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

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);`
mo0926

Posts: 7
Joined: Thu Jan 09, 2020 3:57 pm

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 training zeroes, you're changing the numeric 20050000 value to 2005. That's why my example returned a STRING8, NOT and UNSIGNED8 value.

HTH,

Richard
rtaylor

Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

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

Posts: 7
Joined: Thu Jan 09, 2020 3:57 pm