topic Re: Changing Character-Type Column to Numeric-Type (Date-Group) in SAS Programming

Changing Character-Type Column to Numeric-Type (Date-Group)

u787bruw — Wed, 05 Apr 2023 14:48:40 GMT

* Create dataset;
data test;
	input var1 $10.;
	datalines;
	2023324
	2023324
	2023324
	2023324
	2023324
	20231230
	;
run;

* Alter data;
data test;
	set test;
	/* Modify timestamp format for 'CREATE_TIMESTAMP' */
	YEAR	= SUBSTR(var1, 1, 4);
	MONTH 	= SUBSTR(var1, 5, 1);
	DAY 	= SUBSTR(var1, 6, 2);
	FORM_CREATE_TIMESTAMP = CATS(YEAR, '-', MONTH, '-', DAY);
	DATE_CREATE_TIMESTAMP = INPUT(FORM_CREATE_TIMESTAMP, YYMMDD9.);
	FORMAT DATE_CREATE_TIMESTAMP YYMMDD10.;
run;

I have a column in a SAS dataset that is a 'character-type' and the values look something like '2020213'. All the values appear in the format, YYYYMDD, so I am concerned that if I convert the column like I did in the code above that it will screw up the values that have double-digit months or single-digit days (which it did) in my test above. How can I convert the values so that they look something more like:
'2022320' --> '2022-03-20'

'2022123' --> '2022-12-3'

I have fiddled around with doing something like this:

CHAR_DATE_STRIPPED = STRIP(CHAR_DATE);
DATE1 = INPUT(CHAR_DATE_STRIPPED, YYMMDD8.);
FORMAT DATE1 YYMMDD9.;

But I have gotten zero luck. The values are coming out as "."

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

Reeza — Wed, 05 Apr 2023 14:36:35 GMT

You cannot with any certainty.

Is 2023111
January 11, 2023 or November 1, 2023?

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

u787bruw — Wed, 05 Apr 2023 14:37:59 GMT

That would be January 11, 2023. The format of the values are 'year-month-day'.

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

Tom — Wed, 05 Apr 2023 14:46:22 GMT

Why did you decide to divide the three ones into 11/1 instead of 1/11 ?

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

u787bruw — Wed, 05 Apr 2023 14:50:31 GMT

I am sorry, but I do not quite understand your question. Hopefully I can try to answer it, but the data I am working on is not created by me. The example I just gave above mimics the data I am working on

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

Tom — Wed, 05 Apr 2023 14:51:14 GMT

How do you know if a string like 2022123 should be treated as 2022-1-23 or 2022-12-3. Either meaning is possible.

Do you have extra information that can help you make a choice? For example do you know the day of the month should never be more than one digit? Or is there some other date field you can use to compate it to?

Where did these strings come from? What process was used to create them? Can you get at the original data instead?

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

u787bruw — Wed, 05 Apr 2023 14:55:40 GMT

Ah okay, now I understand the question. I know the data is treated as year-month-day because it is in the same sort of format as other columns that are numeric-type, meaning that they are 'YYYY-MM-DD'. I would like to just copy those columns into this, but the problem arises if the the data is not exactly the same. The dates could be one-day off for example.

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

PaigeMiller — Wed, 05 Apr 2023 15:00:54 GMT

@u787bruw wrote:

Ah okay, now I understand the question. I know the data is treated as year-month-day because it is in the same sort of format as other columns that are numeric-type, meaning that they are 'YYYY-MM-DD'. I would like to just copy those columns into this, but the problem arises if the the data is not exactly the same. The dates could be one-day off for example.

@u787bruw As far as I can see, you have not answered @Tom 's question. It seems he doesn't understand the meaning of 2022123, nor do I, and your reply doesn't shed any additional light on the matter. What is the date associated with 2022123?

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

Tom — Wed, 05 Apr 2023 15:00:35 GMT

@u787bruw wrote:

Ah okay, now I understand the question. I know the data is treated as year-month-day because it is in the same sort of format as other columns that are numeric-type, meaning that they are 'YYYY-MM-DD'. I would like to just copy those columns into this, but the problem arises if the the data is not exactly the same. The dates could be one-day off for example.

Copy?

If you want to convert a string of digits in the style YYYYMMDD into a DATE you can use the YYMMDD informat. Once you have date value you can use any of the many date formats to have the number display in a way that humans will recognize as a date.

datevar=input(stringvar,yymmdd10.);
format datevar yymmdd10.;

But if the digits strings do not represent an actual date, 20223456 for example, ; or are ambiguous about what date they represnet, 2022123, for example, then you need more information to convert them.

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

ghosh — Wed, 05 Apr 2023 15:21:16 GMT

Your character input values are not consistent, they range between lengths 7 and 8. so it they were the same you could use:

data test;
infile datalines;
input var1 $;
	ymd=input(var1,yymmdd8.);
format ymd yymmdd10.;

datalines;
20230324
20231124
20231024
20230924
20230624
20231230
;
proc print;
run;

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

ghosh — Wed, 05 Apr 2023 15:28:44 GMT

In case the data consists of inconsistent lengths, then check out this solution:

https://stackoverflow.com/questions/25213096/changing-date-formats-to-yymmdd8-in-sas-for-date-calculations

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

Reeza — Wed, 05 Apr 2023 15:52:08 GMT

So the days have leading zeroes?

How would Nov 1, 2023 be in the data?

Re: Changing Character-Type Column to Numeric-Type (Date-Group)

Reeza — Wed, 05 Apr 2023 15:53:14 GMT

Not one day off, months off if you cannot differentiate between the days/months.