Tue Feb 25, 2020 3:15 am
Login Register Lost Password? Contact Us


Working with dates

Questions around writing code and queries

Mon Feb 03, 2020 10:15 pm Change Time Zone

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

Tue Feb 04, 2020 2:53 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

Tue Feb 04, 2020 3:40 pm Change Time Zone

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

Wed Feb 05, 2020 2:41 pm Change Time Zone

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
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

Thu Feb 06, 2020 4:29 pm Change Time Zone

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

Thu Feb 06, 2020 6:19 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

Mon Feb 10, 2020 6:21 pm Change Time Zone

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

Mon Feb 10, 2020 6:54 pm Change Time Zone

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
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1519
Joined: Wed Oct 26, 2011 7:40 pm

Thu Feb 13, 2020 6:02 pm Change Time Zone

Thank you, Richard. That helped clear some of the errors I was getting.
mo0926
 
Posts: 7
Joined: Thu Jan 09, 2020 3:57 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 0 guests

cron