DATA Step, Macro, Functions and more

How to extract a date stamp from excel file inside

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

How to extract a date stamp from excel file inside

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,

 


Accepted Solutions
Solution
‎07-12-2017 07:30 PM
Frequent Contributor
Posts: 103

Re: How to extract a date stamp from excel file inside

[ Edited ]

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.


screenshot_18.png
________________________

- That still only counts as one -

View solution in original post


All Replies
Super User
Posts: 17,784

Re: How to extract a date stamp from excel file inside

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

Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

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

Super User
Posts: 17,784

Re: How to extract a date stamp from excel file inside

I can't guess what you want.

Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

/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

 

Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

Please let me know if you need any more clarification.

Super User
Posts: 6,928

Re: How to extract a date stamp from excel file inside

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

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.

Frequent Contributor
Posts: 103

Re: How to extract a date stamp from excel file inside

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;
________________________

- That still only counts as one -

Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

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;

 

Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

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;

Solution
‎07-12-2017 07:30 PM
Frequent Contributor
Posts: 103

Re: How to extract a date stamp from excel file inside

[ Edited ]

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.


screenshot_18.png
________________________

- That still only counts as one -

Contributor
Posts: 71

Re: How to extract a date stamp from excel file inside

Thank you All for your help

Frequent Contributor
Posts: 125

Re: How to extract a date stamp from excel file inside

@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!!!

Super User
Posts: 17,784

Re: How to extract a date stamp from excel file inside

@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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 251 views
  • 4 likes
  • 5 in conversation