topic Re: Split a character variable into a character and a date mmdd in SAS Procedures

Split a character variable into a character and a date mmdd

KayeMcK — Wed, 21 Apr 2021 14:29:44 GMT

I have a file where my date came in as a character and I need to make it a date to use it in Forecasting.

My variable date looks like this:

Friday, April 8

Saturday, April 9

Sunday, April 10

...

What code would I use to make a character variable for DOW and a second variable for the date mmdd?

Thanks,

K-

Re: Split a character variable into a character and a date mmdd

Reeza — Wed, 21 Apr 2021 14:34:35 GMT

Do you have a year component? Did you try ANYDTDTE?

If not, add a year component and give that a try:

x = input(catt(orig_variable, ", 2021"), anydtdte.);

Otherwise, the SCAN() function will separate the components.

date = input(catt(scan(orig_variable, 2, ","), ", 2021"), anydtdte.);
DOW = dow(y);

Re: Split a character variable into a character and a date mmdd

KayeMcK — Wed, 21 Apr 2021 14:50:51 GMT

Thanks Reeza,

I do have the year in a different column (2005). I attached the file.

I tried this code and got the NewDate variable, but only got what I believe is a julian date (19449) in the cells that have a repeated date... Wednesday, Apr 13(1) and Wednesday, Apr 13(2)

The DOW returned an error.

data Spring21.DC2;
set Spring21.chcdata_catcomb;
NewDate = input(catt(scan(Date,2,","),",2005"),anydtdte.);
/* DOW = dow(y); */
proc contents data=spring21.DrewC2;
run;

Re: Split a character variable into a character and a date mmdd

KayeMcK — Wed, 21 Apr 2021 14:51:37 GMT

Sorry, code here...
data Spring21.DC2;
set Spring21.chcdata_catcomb;
NewDate = input(catt(scan(Date,2,","),",2005"),anydtdte.);
/* DOW = dow(y); */
proc contents data=spring21.DC2;
run;

Re: Split a character variable into a character and a date mmdd

Reeza — Wed, 21 Apr 2021 15:18:25 GMT

data Spring21.DC2;
set Spring21.chcdata_catcomb;
NewDate = input(catt(scan(Date,2,","),",2005"),anydtdte.);
DOW = dow(newDate); 
dayofweek = scan(date, 1, ",");
format newDate yymmdd10.;
run;

proc contents data=spring21.DrewC2;
run;

Re: Split a character variable into a character and a date mmdd

ballardw — Wed, 21 Apr 2021 16:36:43 GMT

@KayeMcK wrote:

Thanks Reeza,

I do have the year in a different column (2005). I attached the file.

I tried this code and got the NewDate variable, but only got what I believe is a julian date (19449) in the cells that have a repeated date... Wednesday, Apr 13(1) and Wednesday, Apr 13(2)

--

The DOW returned an error.

--

data Spring21.DC2;
set Spring21.chcdata_catcomb;
NewDate = input(catt(scan(Date,2,","),",2005"),anydtdte.);
/* DOW = dow(y); */
proc contents data=spring21.DrewC2;
run;

You often have to provide an explicit length with anydtdte.

Consider:

data junk;
   date= "Friday, April 8";
   NewDate = input(catx(',',scan(Date,2,","),"2005"),anydtdte21.);   
   put  newdate date9.;
run;

since CATX strips trailing spaces like CATT and is designed to insert a string between values I prefer that but the key bit is the length of anydtdte format because it will default to 9 characters which is not enough to read a text month, day of month and a couple of spaces or commas plus the year.

Re: Split a character variable into a character and a date mmdd

KayeMcK — Wed, 21 Apr 2021 15:41:41 GMT

Thank you so much.

I am almost there!

I have some dates that were appended with a (1) or a (2). I just need to strip that off before adding the year. Everything else is working!

the anydtdte21. the "21" is what fixed the last error I was having.

K-

Re: Split a character variable into a character and a date mmdd

KayeMcK — Wed, 21 Apr 2021 15:48:08 GMT

Thanks again !!!
It may be rough, but I split the variable again and it works!!!
THANK YOU!!!

data DC2;
set chcdata_catcomb;
Date1 = scan(Date,2,",");
Date2 = scan(Date1,1,"(");
NewDate = input(catt(Date2,",2005"),anydtdte21.);
dayofweek = scan(date, 1, ",");
format NewDate yymmdd10.;
run;
proc contents data=DC2;
run;