Working with dates
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
For example: output all complete dobs such as 20051201... etc in a column and not partially incomplete dobs like 20050000
- mo0926
- Posts: 18
- 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:
HTH,
Richard
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
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- 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: 18
- 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
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
- Community Advisory Board Member
- Posts: 1619
- 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
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: 18
- 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:
HTH,
Richard
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
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- 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: 18
- Joined: Thu Jan 09, 2020 3:57 pm
mauricexxvi,
This line of code:
Also, this line:
HTH,
Richard
This line of code:
- Code: Select all
Unsigned8 dob := (Unsigned8)indob;
- Code: Select all
STRING8 dob := (STRING8)indob;
Also, this line:
- Code: Select all
Return (Unsigned8)(YY + MM + DD);
HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
Thank you, Richard. That helped clear some of the errors I was getting.
- mo0926
- Posts: 18
- Joined: Thu Jan 09, 2020 3:57 pm
If a date is a number its trivial to extract components of a date.
Assuming dates are of the form YYYYMMDD
then:
Also dates should be UNSIGNED not INTEGER.
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.
- Allan
- Posts: 444
- Joined: Sat Oct 01, 2011 7:26 pm
11 posts
• Page 1 of 2 • 1, 2
Who is online
Users browsing this forum: Google [Bot] and 1 guest