topic Re: Trouble Converting character data into date YYMMDD in SAS Programming

Trouble Converting character data into date YYMMDD

Bankshot — Thu, 30 Aug 2018 00:15:22 GMT

Hi,

I'm having a tough time converting a couple of character variable fields into date fields. Each of the variables listed below are in the format of "2017-01-31" in character format. I tried to manually convert it but it's not working. Each of the _new variables I created only removed the "-" sign but still have blank spaces in them despite including the trim() function.

data new;
	set raw;

	Opened_dt_new = trim(tranwrd(opened_dt, "-", ""));
	Maturity_dt_new = trim(tranwrd(maturity_dt, "-", ""));
	Fico_dt_new = trim(tranwrd(fico_dt, "-", ""));
	Expiration_dt_new = trim(tranwrd(expiration_dt, "-", ""));

	drop opened_dt maturity_dt Fico_dt expiration_dt;

	Opened_dt = input(Opened_dt_new, YYMMDD10.);
	format Opened_dt YYMMDD10.;
	Maturity_dt = input(Maturity_dt_new, YYMMDD10.);
	format Maturity_dt YYMMDD10.;
	Fico_dt = input(Fico_dt_new, YYMMDD10.);
	format Fico_dt YYMMDD10.;
	Expiration_dt = input(Expiration_dt_new, YYMMDD10.);
	format Expiration_dt YYMMDD10.;

run;

Due to the fact that the _new variables have space in it, Format $YYMMDD was not found or could not be loaded. What is wrong here?

Re: Trouble Converting character data into date YYMMDD

Bankshot — Thu, 30 Aug 2018 00:18:05 GMT

For further clarification, after the using the

trim(tranwrd(opened_dt, "-", ""))

method, the output looks like

opened_dt

2017 01 31;

where I'd like it to be:

20170131;

Re: Trouble Converting character data into date YYMMDD

Reeza — Thu, 30 Aug 2018 00:36:48 GMT

1. Use COMPRESS() to remove the - if you want

2. You cannot convert and save back to the same name, it has to have a new name. Yes it's annoying but it's the rules for now. You can rename your old variables before and then assign them the old name in the conversion is a common work around.

3. You do not need to remove the - ahead of time, you can use INPUT directly on it.

This is what you need to do, note that it looks the exact same but you have different variable types.

data demo;
date_have= '2017-01-31';

date_want = input(date_have, yymmdd10.);
format date_want yymmdd10.;


run;

*check your types and formats;
proc contents data=demo;
run;

@Bankshot wrote:

Hi,

I'm having a tough time converting a couple of character variable fields into date fields. Each of the variables listed below are in the format of "2017-01-31" in character format. I tried to manually convert it but it's not working. Each of the _new variables I created only removed the "-" sign but still have blank spaces in them despite including the trim() function.
data new;
	set raw;

	Opened_dt_new = trim(tranwrd(opened_dt, "-", ""));
	Maturity_dt_new = trim(tranwrd(maturity_dt, "-", ""));
	Fico_dt_new = trim(tranwrd(fico_dt, "-", ""));
	Expiration_dt_new = trim(tranwrd(expiration_dt, "-", ""));

	drop opened_dt maturity_dt Fico_dt expiration_dt;

	Opened_dt = input(Opened_dt_new, YYMMDD10.);
	format Opened_dt YYMMDD10.;
	Maturity_dt = input(Maturity_dt_new, YYMMDD10.);
	format Maturity_dt YYMMDD10.;
	Fico_dt = input(Fico_dt_new, YYMMDD10.);
	format Fico_dt YYMMDD10.;
	Expiration_dt = input(Expiration_dt_new, YYMMDD10.);
	format Expiration_dt YYMMDD10.;

run;
Due to the fact that the _new variables have space in it, Format $YYMMDD was not found or could not be loaded. What is wrong here?

Re: Trouble Converting character data into date YYMMDD

Kurt_Bremser — Thu, 30 Aug 2018 06:39:06 GMT

Actually, if you want it to look exactly the same, there needs to be a slight change to the format:

format date_want yymmddd10.;

as the standard yymmdd format will use slashes instead of dashes.

Now that's almost poetry. Slashes/dashes 🙂

Re: Trouble Converting character data into date YYMMDD

Bankshot — Thu, 30 Aug 2018 17:07:10 GMT

"Slashes/dashes" I like that. You should trademark that