topic Re: Converting three types of date information in numeric form to one date form? in New SAS User

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

disosa — Mon, 11 Nov 2019 20:46:50 GMT

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

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

Reeza — Mon, 11 Nov 2019 20:51:00 GMT

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?

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

Reeza — Mon, 11 Nov 2019 20:51:07 GMT

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-data-AKA-generate/ta-p/258712

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

Tom — Mon, 11 Nov 2019 21:36:07 GMT

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;

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

unison — Mon, 11 Nov 2019 21:46:03 GMT

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