topic Re: How to extract a date stamp from excel file inside in SAS Programming

How to extract a date stamp from excel file inside

buddha_d — Wed, 12 Jul 2017 00:56:36 GMT

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,

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

Reeza — Wed, 12 Jul 2017 02:02:00 GMT

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

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

buddha_d — Wed, 12 Jul 2017 03:16:16 GMT

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

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

Reeza — Wed, 12 Jul 2017 03:17:38 GMT

I can't guess what you want.

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

buddha_d — Wed, 12 Jul 2017 03:22:42 GMT

/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

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

buddha_d — Wed, 12 Jul 2017 03:23:59 GMT

Please let me know if you need any more clarification.

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

Kurt_Bremser — Wed, 12 Jul 2017 06:05:09 GMT

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.

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

buddha_d — Wed, 12 Jul 2017 11:03:50 GMT

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.

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

Oligolas — Wed, 12 Jul 2017 11:48:36 GMT

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;

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

buddha_d — Wed, 12 Jul 2017 12:30:47 GMT

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;

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

buddha_d — Wed, 12 Jul 2017 12:32:38 GMT

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;

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

Oligolas — Wed, 12 Jul 2017 13:07:26 GMT

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.

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

Reeza — Wed, 12 Jul 2017 14:39:43 GMT

Specify the RANGE option on PROC IMPORT. You can use the RANGE to specify a single cell to import each of the dates individually from the Excel file. Tedious but no real way around something like that.

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

buddha_d — Wed, 12 Jul 2017 23:32:46 GMT

Thank you All for your help

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

Cruise — Thu, 27 Jul 2017 18:21:49 GMT

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

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

Reeza — Thu, 27 Jul 2017 19:03:26 GMT

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

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

Cruise — Thu, 27 Jul 2017 19:28:38 GMT

@Reeza

Hi Reeza,

Thanks for your comment. I will create a new post if i can't figure it out myself in the next hour or so.

As per your inquiries,
"Date of birth" variable in excel was imported to "15MAR2014:00:00:00" DATETIME19. format in SAS. I tried below approaches so far:

Approach1:

date=datepart(date_of_birth);
date1=input(put(date,8.),yymmdd8.);

Result: date1 variable with lots of sporadic missing with 5 digit numbers such as: 15367.

Approach2:

Shifting starting point in original excel file then import.

SAS_birthdate = excel_birthdate - 21916;

Result2: The same. Bunch of numbers in the close range of 15540 here and there with huge missing.

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

Reeza — Thu, 27 Jul 2017 19:39:16 GMT

date=datepart(date_of_birth);
format date date9.;

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

Cruise — Thu, 27 Jul 2017 20:33:57 GMT

Thank you very much. It solved the problem.