topic Need help extracting date out of a character variable in SAS Programming

Need help extracting date out of a character variable

tolapa — Wed, 19 Dec 2018 14:54:50 GMT

Hello,

Good Evening.

I am trying to extract date from a string variable. The variable is called period_desc. It looks like below. I want the date part out of it in mmddyy6. format or any other date format.

period_desc

1 WEEK ENDING 10/28/2017

1 WEEK ENDING 11/04/2017

I want just the date part out of it. Here is what I have done so far. i did proc contents to check the type of variables that are being produced and it doesn't seem to be doing the job. P.S I used this code after reading it on this forum but not working for me.

#	Variable	Type	Len	Format
4	DATE	Num	8	MMDDYY10.
3	DATE1	Char	24
2	period_desc	Char	24
1	period_id	Num	8

Please help!

data period    ;
infile "&input_dir_path.us_period.csv" delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;
input
             period_id :best12.
             period_desc :$24.
             period_type :$4.
             period_start_date :yymmdd10.
             period_end_date :yymmdd10.
             load_execution_id :best12.
             last_update_datetime :datetime.;
run;

data period_formatted (keep=period_id period_desc DATE1  DATE);
set period;
DATE1 = trim(substr(period_desc,14,11));
DATE = input(DATE1,MMDDYY10.);
format DATE mmddyy10.;
run;

Re: Need help extracting date out of a character variable

novinosrin — Wed, 19 Dec 2018 14:58:53 GMT


data have;
input period_desc $50.;
cards;
1 WEEK ENDING 10/28/2017
1 WEEK ENDING 11/04/2017
;
data want;
set have;
date=input(scan(period_desc,-1,' '),mmddyy10.);
format date mmddyy10.;
run;

Re: Need help extracting date out of a character variable

PaigeMiller — Wed, 19 Dec 2018 15:02:08 GMT

i did proc contents to check the type of variables that are being produced and it doesn't seem to be doing the job.

It's not doing the job? You have given us no information about the problem you are experiencing. Explain this. What isn't working? What result do you want that you are not getting? Your PROC CONTENTS shows you have a variable named DATE that is numeric and formatted as a date.

Could it be that you really want this?

DATE1 = trim(substr(period_desc,15,10));

Or even better yet

date1 = scan(period_desc,-1,' ');