Change dates format then get the difference between them.

[missaka]

Hi, need some help with two variables, both displaying dates in the following format: DDMMYYYY. (Using SAS UE)

Example:
 - DTOBITO = 04062017, which means "04jun2017"
 - DTNASC = 06062017, which means "06jun2017"

What i need to do is find a way to get the difference(in days) between DTOBITO and DTNASC , like this:

var3 = DTOBITO - DTNASC.

Using the example, var3 should be equal to 2.

Im using a dbf file, so im not certain about the format of this variable, if its numeric ou char variables.

[PGStats]

Assuming they are simple numbers:

data test;
DTOBITO = 04062017;
DTNASC = 06062017;
DT_OBITO = mdy(mod(int(DTOBITO/10000),100), int(DTOBITO/1000000), mod(DTOBITO, 10000));
DT_NASC = mdy(mod(int(DTNASC/10000),100), int(DTNASC/1000000), mod(DTNASC, 10000));
format DT_: yymmdd10.;
diff = DT_NASC - DT_OBITO;
run;

proc print; run;

Obs. 	DTOBITO 	DTNASC 	DT_OBITO 	DT_NASC 	diff
1 	4062017 	6062017 	2017-06-04 	2017-06-06 	2

[PeterClemmensen]

data _null_;
	DTOBITO=04062017;
	DTNASC=06062017;

	_DTOBITO=input(put(DTOBITO, $8.), ddmmyy8.);
	_DTNASC=input(put(DTNASC, $8.), ddmmyy8.);

	diff=_DTNASC-_DTOBITO;

	put diff=;
run;

[Jagadishkatam]

Alternatively please try

data test;
DTOBITO=04062017;
DTNASC =06062017;
DT_OBITO=input(strip(prxchange('s#(\d{1,2})(\d{2})(\d{4})#$3-$2-$1#',-1,put(DTOBITO,best.))),yymmdd10.);
DT_NASC=input(strip(prxchange('s#(\d{1,2})(\d{2})(\d{4})#$3/$2/$1#',-1,put(DTNASC,best.))),yymmdd10.);
format DT_: yymmdd10.;
diff = DT_NASC - DT_OBITO;
run;

[Patrick · Accepted solution]

Proc Contents will show you if the variables are numeric or character. Once you know this I'd go for an approach where you first convert the strings or numbers into a SAS date value as then you can simply substract the dates from each other.

@PeterClemmensen posted an approach for numeric SAS variables. If they are character then simply omit the put() statement part in the the code (which converts the numerical vars to a string before reading the string into SAS as a SAS date value).

[missaka]

I wish i could select more than one reply as accepted solution. 😕 You and @draycut resolved the problem. Thank you very much.

I still had a doubt about part of the code: 

The "proc contents" show me that all vars were type Char. When i run the code with the "put diff=" line, i get a "WARNING: Maximum log size exceeded." message. If i comment the "put diff=" line, the warning disapears and the results remains the same. What is the purpose of this line?

[ballardw]

---

@missaka wrote:

I wish i could select more than one reply as accepted solution. 😕 You and @draycut resolved the problem. Thank you very much.

I still had a doubt about part of the code: 

The "proc contents" show me that all vars were type Char. When i run the code with the "put diff=" line, i get a "WARNING: Maximum log size exceeded." message. If i comment the "put diff=" line, the warning disapears and the results remains the same. What is the purpose of this line?

---

If you have a line in a data step like:

put diff=;

then when the code you will display the value of the variable(s) in the log each time the instruction is encountered. One very common use of the Put is debugging a program, checking on values at one or more places in the execution to see if the code is doing what is expected. The put statement can also be used to write more interesting messages or write to an external file with lots of text control.

The put could execute once, or more times, per record in your data. If you have something like that inside a do loop  in the data step you get one line put to the log per execution of the loop.

If you put enough lines then you can exceed the amount of memory that the log is intended to hold. So you get a warning like that. Removing the put means you aren't writing all that information in the log and so don't get the warning.

[Tom]

Are you sure they are not already dates that are just using a format that makes them appear in that style?

Run PROC  CONTENTS on your SAS dataset and check if the variable is numeric or character and if it is numeric what (if any) format is attached to it.