topic Re: Combining 3 variables into one and converting to date in New SAS User

Combining 3 variables into one and converting to date

AMMAN — Thu, 15 Nov 2018 23:17:45 GMT

I need to take three numeric variables, combine into one, then convert to a date. I am using a CATX and am getting a warning I don't understand.

data IncurredDate;
set work.AngieClaims;
date=input(catx("/", Mnth, Date, Year), mmddyy10);
format date mmddyy10;
run;

Log and input file attached.

Thanks for your help.

Re: Combining 3 variables into one and converting to date

kiranv_ — Thu, 15 Nov 2018 23:50:51 GMT

just use mdy function

data IncurredDate;
set work.AngieClaims;
newdate=mdy( Mnth, Date, Year);
format newdate mmddyy10.;
run;

Re: Combining 3 variables into one and converting to date

AMMAN — Thu, 15 Nov 2018 23:48:23 GMT

Thanks for the recommendation. I tried that and got this result. I don't see the combined variable

Re: Combining 3 variables into one and converting to date

kiranv_ — Thu, 15 Nov 2018 23:53:36 GMT

try making new column name as date and I have updated my answer

Re: Combining 3 variables into one and converting to date

AMMAN — Fri, 16 Nov 2018 00:03:11 GMT

I dropped some vars to clean it up. New var shows up, but it empty

data IncurredDate (drop=MOS DOS YOS Mnth Date Year) ;
set work.AngieClaims;
newdate=mdy(Mnth, Date, Year);
format newdate mmddyy10.;
run;

Re: Combining 3 variables into one and converting to date

kiranv_ — Fri, 16 Nov 2018 01:47:58 GMT

see the below example . even if you have data in character values this works albite it gives note in log.(first 2 examples work)

unless some of your does not make date, which cause this issue. check the examples in code below

data want;
mnth = 1;
d = 2;
yr =2016;
newdate=mdy(mnth,d,yr);
format newdate mmddyy10.;
run;

data want1;
mnth = "1";
d = "2";
yr ="2016";
newdate=mdy(mnth,d,yr);
format newdate mmddyy10.;
run;
/* this will not work because there is 13 as month and result in . and you have note in 
log NOTE: Invalid argument to function MDY(13,2,2016) at line 77 column 9.*/
data want3;
mnth = 13;
d = 2;
yr =2016;
newdate=mdy(mnth,d,yr);
format newdate mmddyy10.;
run;

Re: Combining 3 variables into one and converting to date

AMMAN — Fri, 16 Nov 2018 01:33:28 GMT

Ooof, I don't understand your feedback.

Re: Combining 3 variables into one and converting to date

kiranv_ — Fri, 16 Nov 2018 01:41:54 GMT

did u run the codes. first 2 work if your month, day and year are allowable then mdy code work.

Re: Combining 3 variables into one and converting to date

andreas_lds — Fri, 16 Nov 2018 05:43:31 GMT

@AMMAN wrote:

Ooof, I don't understand your feedback.

Start with reading the log of your program: are there any notes about missing values? The function mdy need three numbers to create a data, if one is missing the result is missing, too. @kiranv_ tried to point to the most likely cause of having missing values: your data.

Re: Combining 3 variables into one and converting to date

Tom — Fri, 16 Nov 2018 06:15:58 GMT

From the picture you posted your MONTH variable is named MNTH, your DAY variable is named DOS and your YEAR variable is named YOS.

So the assignment statement you want would be

date=mdy(mnth,dos,yos);

It looks like you probably ran this code instead

date=mdy(mnth,dos,year);

So SAS created a new variable named YEAR that has all missing values. And also made DATE as missing since you did not provide a year value.

Re: Combining 3 variables into one and converting to date

Tom — Fri, 16 Nov 2018 13:16:22 GMT

Because you are still trying to use a variable that doesn't exist for the year number.

Re: Combining 3 variables into one and converting to date

AMMAN — Fri, 16 Nov 2018 22:04:27 GMT

I was confused by the labels. I couldn't understand why it said I was missing values. I was looking at the wrong variable. Thanks for your help!

Re: Combining 3 variables into one and converting to date

AMMAN — Fri, 16 Nov 2018 22:04:57 GMT

Thanks for your help!

Re: Combining 3 variables into one and converting to date

AMMAN — Fri, 16 Nov 2018 22:05:29 GMT

Thanks for your help. I was looking at the wrong variable.