How to extract a date stamp from excel file inside

[buddha_d]

How can I extract date (07-11-2016) from this excel file from this data and make a sas data with different format and lengths?

If you can send the SAS code that would be great and I am sending dummy data.

Thanks,

[Reeza]

DATEPART() will extract the date from a datetime field.

[buddha_d]

Date is in the header row as well as in the 4 or 5 th row. Datepart don't work there

[Reeza]

I can't guess what you want.

[buddha_d]

/orry Reeza, I want to extract date 07-11-2016 date stamp extracted from this excel sheet (it is there in firstrow , title column, datetime and second column (where you see date: 07-11-2016). I need to see this date stamp along with other variables. I was able to import data , but time stamp is the one giving me challenges.

thanks

[buddha_d]

Please let me know if you need any more clarification.

[Kurt_Bremser]

Export from Excel to a text file (csv), and read that with a custom data step.

- the exported file can be viewed with any text editor

- the file format of csv does not change at the whim of Microsoft

- consistent data structure is guaranteed, changes in the Excel file will result in SAS messages

Doing the transfer from Excel to SAS in this way has been proven to be the best option for > 2 decades here.

[buddha_d]

Thank you Kurt. You might be correct to export data from EXCEL to CSV and then extract date. But my assignment is to get from EXCEL. Thank you so much for replying.

[Oligolas]

options obs=1;
PROC IMPORT OUT= WORK.tmp 
            DATAFILE= "C:\Temp\DUMMY DATA.xlsx" 
            DBMS=EXCEL REPLACE;
     RANGE="Sheet1$"; 
     GETNAMES=NO;
     MIXED=NO;
     SCANTEXT=NO;
     USEDATE=NO;
     SCANTIME=NO;
RUN;
options obs=max;

data tmp;
   set tmp;
   length date $32;
   array all _character_;
   call missing(date);
   do over all;
      if prxmatch('/.*(\d\d-\d\d-\d\d\d\d).*/',all) then date=prxchange('s/.*(\d\d-\d\d-\d\d\d\d).*/$1/',-1,all);
   end;
   keep date;
run;

[buddha_d]

Oligolas, Thanks for responding with code. I can extract the date Yah !!!! 

Thank you so much. Can you share your logic in the second tmp dataset. I am bit baffled by it. thanks

array all _character_;
   call missing(date);
   do over all;
      if prxmatch('/.*(\d\d-\d\d-\d\d\d\d).*/',all) then date=prxchange('s/.*(\d\d-\d\d-\d\d\d\d).*/$1/',-1,all);
   end;
   keep date;
run;

[buddha_d]

Oligolas, Thanks for responding with code. I can extract the date Yah !!!! 

Thank you so much. Can you share your logic in the second tmp dataset. I am bit baffled by it. thanks

array all _character_;
call missing(date);
do over all;
if prxmatch('/.*(\d\d-\d\d-\d\d\d\d).*/',all) then date=prxchange('s/.*(\d\d-\d\d-\d\d\d\d).*/$1/',-1,all);
end;
keep date;
run;

[Oligolas · Accepted solution]

Hi,

I'm glad it helps.

Logic:

Define an array containing the character variables of the dataset

Loop over all these variables

For each of them, look with a regular expression if they contain something that looks like a date

If yes (I expect only one of these variables to contain a date), extract the date.

's/.*(\d\d-\d\d-\d\d\d\d).*/$1/' means:

s/ start of the string

.* any character

(\d\d-\d\d-\d\d\d\d) if anything is found that looks like a date, capture it, in the first group. Brackets determine a group.

.* any character

/ end of the string

$1 the replacement string (my first capturing group)

/ end of the regex

That all means:

Look for something that starts with anything, I don't mind what,

followed by something that looks like a date,

followed by anything, I don't mind what

If you have it, replace this stuff by the thing that looks like a date (= extract the date)

and write it out to the 'date' variable.

Maybe take a look at this tutorial if you're not familiar with regular expressions:

https://www.youtube.com/watch?v=WtQLRGW_W2A

Cheers.

[buddha_d]

Thank you All for your help

[Cruise]

@Oligolas

Hi Oligolas,

Excellent solution. I have almost the same problem except only difference is my excel date is in d/mm/yyyy format. The same code you provided resulted in sas data with date variable blank. Could you please help modify your prxmatch code in the context of my data attached here? 

Desirable output would be a sas data that contains all variables from the excel file with "date of birth" variable converted to any sas date format. 

Thanks!!!

[Reeza]

@Cruise Your problem doesn't appear to the be the same. He had some unformatted excel files with dates in mulitple columns. You appear to have a well structured file and can use much simpler approaches. 

For starters, what type/format are your dates imported in? You should start your own question or try and find an example that better aligns with your question. This is like using a hammer with a thumbtack approach.