topic Re: Concatenating Dates in SAS Programming

Concatenating Dates

wheddingsjr — Wed, 28 Apr 2021 20:29:23 GMT

Hi all

I imported data into SAS which looks like this:

ID	AdmitDate	LastRecordDate
847545	11/1/2020	11/7/2020

I used the following code:

data combined;
set work.Combined;
iTox_event_enc = CATX("-", MRN, AdmitDate, LastRecordDate);
run;

The results look like: 847545-22246-22253 instead of 847545-11/01/2020-11/07/2020. is there a way to fix that?

In another import instead of it looking like this after the import

ID	AdmitDate	LastRecordDate
847545	11/1/2020	11/7/2020

It came out like this:

ID	Start_EventDTS	LastRecordDate
847545	11/01/2020	44224

Both original source documents are formatted the same but they imported differently. I want to run the same concatenation but it gives me more problems than the first import.

Any help would be appreciated.

Thanks

Re: Concatenating Dates

Reeza — Wed, 28 Apr 2021 20:56:04 GMT

CATX uses the underlying data, so nest it with a PUT statement to have the format honoured.

From the documentation:

item

specifies a constant, variable, or expression, either character or numeric. If item is numeric, its value is converted to a character string by using the BESTw. format. In this case, SAS does not write a note to the log.

Something like the follow should work, you can play around with the exact format you need.

iTox_event_enc = CATX("-",
                                              MRN, 
                                             put(AdmitDate, mmddyyd10.),
                                             put(LastRecordDate, mmddyyd10.)
                                        );

@wheddingsjr wrote:

Hi all

I imported data into SAS which looks like this:

ID AdmitDate LastRecordDate

847545 11/1/2020 11/7/2020

I used the following code:
data combined;
set work.Combined;
iTox_event_enc = CATX("-", MRN, AdmitDate, LastRecordDate);
run;
The results look like: 847545-22246-22253 instead of 847545-11/01/2020-11/07/2020. is there a way to fix that?

In another import instead of it looking like this after the import

ID AdmitDate LastRecordDate

847545 11/1/2020 11/7/2020

It came out like this:

ID Start_EventDTS LastRecordDate

847545 11/01/2020 44224

Both original source documents are formatted the same but they imported differently. I want to run the same concatenation but it gives me more problems than the first import.

Any help would be appreciated.

Thanks

Re: Concatenating Dates

wheddingsjr — Wed, 28 Apr 2021 21:15:45 GMT

Thanks Reeza

I was using date10. and it didn't work but this works for the first part, but it didn't on the second part where the data from the original source is mmddyy, but its being imported as 44224 instead of a date. I tried "input" to change the variable but it doesn't work. It imported as:

ID	Start_EventDTS	LastRecordDate
847545	11/1/2020	11/7/2020

after the import it was:

ID	Start_EventDTS	LastRecordDate
847545	11/01/2020	44224

Re: Concatenating Dates

Reeza — Wed, 28 Apr 2021 21:39:04 GMT

I don't know what a DATE10 format would look like, DATE7 and DATE9 are common.

I was using date10. and it didn't work but this works for the first part, but it didn't on the second part where the data from the original source is mmddyy, but its being imported as 44224 instead of a date. I tried "input" to change the variable but it doesn't work. It imported as:

Show the code you used, something from your original post no longer is true.

This doesn't align with your initial post and the statement that it became this though. That would only happen if both were dates so is that incorrect?

847545-22246-22253 instead of 847545-11/01/2020-11/07/2020. is there a way to fix that?

Re: Concatenating Dates

Reeza — Wed, 28 Apr 2021 21:40:47 GMT

And my guess is you're working with multiple Excel files. If that's the case you need to ensure your formats and types align across the different files after the IMPORT. Excel does not enforce types so you kinda get what you get after a PROC IMPORT and need to ensure it's imported correctly.

Re: Concatenating Dates

wheddingsjr — Wed, 28 Apr 2021 21:42:09 GMT

Reeza

What you replied to was perfect, but I think either all of my post did not go through (though I can see it on my end) because if you read further down my original post I spoke about a second import.

Re: Concatenating Dates

wheddingsjr — Wed, 28 Apr 2021 21:44:29 GMT

Correct, there are two imports. Both were formatted the same, but the second one for some reason imported with that fuky format for the date. I tried converting it using the input function, but to no avail.

Re: Concatenating Dates

Tom — Wed, 28 Apr 2021 22:06:42 GMT

@wheddingsjr wrote:

Correct, there are two imports. Both were formatted the same, but the second one for some reason imported with that fuky format for the date. I tried converting it using the input function, but to no avail.

That is a common problem with reading data from Excel files.

https://communities.sas.com/t5/New-SAS-User/Excel-to-SAS-date-format-issues/td-p/539669

Re: Concatenating Dates

wheddingsjr — Wed, 28 Apr 2021 22:35:09 GMT

Thanks Tom

I tried that and got an error. Here is the code:

PROC IMPORT DATAFILE= "H:\Immunotoxicity\ImmunotoxicityProgram_04.22.2021_Mine.xlsx"
	OUT= work.Patients REPLACE DBMS= XLSX;
	SHEET= Patients_Events;
	RUN;

data Patients;
set work.Patients;
LastRecordDate = input(Date,5.)+ '30DEC1899'd;
format LastRecordDate yymmdd10.;
run;

And I got this error:

31         data Patients;
32         set work.Patients;
33         LastRecordDate = input(Date,5.)+ '30DEC1899'd;
34         format LastRecordDate yymmdd10.;
                                 _________
                                 484
NOTE 484-185: Format $YYMMDD was not found or could not be loaded.

Re: Concatenating Dates

Tom — Wed, 28 Apr 2021 22:42:53 GMT

You also should have gotten other messages about SAS converting numbers into string.

That is because your dataset already has a CHARACTER variable with that name.

You need to create a NEW numeric variable to hold the date value.

Is LastRecordDate the variable that you already have? Or are there 2 date variables in your PATIENTS dataset that you need to convert?

Try something like this:

data Patients;
  set work.Patients;
  newvar = input(LastRecordDate ,5.)+ '30DEC1899'd;
  format newvar yymmdd10.;
  rename newvar=LastRecordDate LastRecordDate = CharDate ;
run;

If you want the new variable to use that same name

Re: Concatenating Dates

wheddingsjr — Wed, 28 Apr 2021 22:48:01 GMT

That worked absolutely PERFECT!!

Thanks Tom

Re: Concatenating Dates

Reeza — Wed, 28 Apr 2021 22:50:36 GMT

Thanks for showing your code.

You need to provide an informat that matches the appearance of the data so you need to apply it to the ones that look like dates, not the ones that are numeric. For the numeric ones you apply a format instead.

If the variable is numeric after import apply a format. If the format doesn't display the correct date, then try adding the adjustment factor, 30Dec1899.
If the variable is character after import, use INPUT to convert, then apply the format. The INPUT() function requires the informat, which tells SAS the currently displayed format, so it should likely be something like MMDDYY10. not 5.