Conflicting date formats when coalescing two variables to a new one in PROC SQL

[SSimonov]

Hi, i've been puzzled by this problem for some time already and half gave up on figuring it out.

Let's say our dataset includes two date variables A and B, and one is missing when other is not. A is in format ddmmmyyy, B is a numeric 4-digit year. For example:

        A             |      B

01jan1990              .

        .                   2003

        .                  1996

08feb2017             .

When I coalesce them and assign to another variable C resulting values are in datetime format, similar to following(values are random, purely for illustration):

C

14735

1992

2003

17631

I want to leave year values as they are, but put 5-digit ones to iso8601 ([dd-mm-yyyy]).

Hoping for community's help.

[Patrick · Accepted solution]

A SAS Date value is a numerical value that is the count of days since 1/1/1960. You then can apply a SAS Date format to make such a count human readable when printing the value.

To create a variable that stores a mix of SAS Date values and year numbers doesn't feel like a good idea and will make working with such a variable just hard (i.e. when trying to sort the table by year). 

I believe it would be much better to convert the Year values to SAS Date values for a date on January 1. Function mdy() allows you to do this easily.

iso8601 has year first. I've used another SAS format that creates the dd-mm-yyyy pattern you've asked for ...but you could simply use another format if you want to change this (this is just how SAS prints the values).

Below some code that hopefully explains things to you. The last variable Mixed_Values_Formatted gives you what you've asked for but I wouldn't recommend to go down this route.  I'd go for SAS_Date_Value_Formatted.

data have;
  infile datalines truncover dsd;
  input a :date9. b;
  format a date9.;
  datalines;
01jan1990,.
.,2003
.,1996
08feb2017,.
; 


proc format;
  value mixed_vals
    low-2099 = [32.]
    other    = [ddmmyyD10.]
    ;
run;
    

data want;
  set have;
  
  SAS_Date_Value=coalesce(a,mdy(1,1,b));

  format SAS_Date_Value_Formatted ddmmyyD10.;
  SAS_Date_Value_Formatted=SAS_Date_Value;

  Year_Number=coalesce(year(a),b);

  Mixed_Values=coalesce(a,b);
  format Mixed_Values_Formatted mixed_vals.;
  Mixed_Values_Formatted=Mixed_Values;

run;

[SSimonov]

Hi, Patrick! The solution I eventually came to is similar to yours in a lot of ways. Formatting year to the SAS Date format first is something I had in mind too, but it seems I approached it in a wrong way. Very helpful insight, thank you.