Concatenating Dates

[wheddingsjr]

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

[Reeza · Accepted solution]

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

---

[wheddingsjr]

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

[Reeza]

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?

[Reeza]

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.

[wheddingsjr]

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.

[Tom]

---

@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

[wheddingsjr]

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.

[Tom]

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

[wheddingsjr]

That worked absolutely PERFECT!! 

Thanks Tom

[Reeza]

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.

[wheddingsjr]

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.