BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buddha_d
Pyrite | Level 9

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11

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
________________________

- Cheers -

View solution in original post

18 REPLIES 18
Reeza
Super User

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

buddha_d
Pyrite | Level 9

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

Reeza
Super User

I can't guess what you want.

buddha_d
Pyrite | Level 9

/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
Pyrite | Level 9

Please let me know if you need any more clarification.

Kurt_Bremser
Super User

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
Pyrite | Level 9

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
Barite | Level 11
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;
________________________

- Cheers -

buddha_d
Pyrite | Level 9

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
Pyrite | Level 9

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
Barite | Level 11

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
________________________

- Cheers -

buddha_d
Pyrite | Level 9

Thank you All for your help

Cruise
Ammonite | Level 13

@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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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