Converting three types of date information in numeric form to one date form?

[disosa]

Hello,

I am working with three different data sets that have date information currently stored in "numeric" form. I would like to convert the date information in all three of these data sets to a SAS-recognized "date" form, both for conformity among data sets and ease of analysis.

Here is an example of the three numeric date formats among the data sets:

My biggest challenge is working with the date format for Dataset 1 in the example. I have tried using input/put functions suggested in other areas of this forum to convert this numeric-type variable to date-type, but SAS seems to be having trouble recognizing this as MMYY format, without DD information.

My overall goal is to have all three data sets have date information in MM/YYYY format.

Please let me know if I can provide any more details, and thank you so much for your assistance.

Diana

[Reeza]

Use LENGTHN() to get the length or convert it to a character to find the length and then conditionally process each type based on the length. What would you expect as results from #1 and #3?

[Reeza]

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Tom]

Please provide your interpretation of what those strings of digits mean.

The first one might by MMYY ?  Are any of them in the form MYY (meaning January thru September)?

Looks like 2 is in YYYYMMDD order.

And perhaps 3 is in YYYYMM order?

Do you want to set the ones without day of the month to the first day of the month?  Also for the second one where you DO have a day of the month do you want to also convert it to the first day of month?

data want ;
  set have ;
  select (length(cats(FAKE_DATE)));
    when (3,4) REAL_DATE=input('01'||put(FAKE_DATE,z4.),ddmmyy6.);
    when (8) REAL_DATE=intnx('month',input(put(FAKE_DATE,z8.),yymmdd8.),0,'b');
    when (6) REAL_DATE=input(put(FAKE_DATE,z6.),yymmn6.);
    otherwise do;
       put 'WARNING: Invalid DATE value. ' FAKE_DATE= ;
    end;
  end;
  format REAL_DATE yymm7. ;
run;

[unison]

I understand you have several datasets but here's how I'd handle it in each case (assuming the first of the month when there is no day):

data have;
	input dataset $ date;
	datalines;
01 1119
02 20191111
03 201911
;
run;

data want;
	set have;
	format date_new date9.;
	date_str = put(left(date),$8.);

	if dataset='01' then date_new=input("01"||date_str,ddmmyy8.);

	if dataset='02' then date_new=input(date_str,yymmdd10.);

	if dataset='03' then date_new=input(date_str,yymmn6.);
run;

-unison