topic Re: Converting character variable to dates , some blank dates in SAS Programming

Converting character variable to dates , some blank dates

d0816 — Thu, 28 Jun 2018 21:48:16 GMT

I have following dataset in excel. Some of the close and end dates are blank because those events have not occurred. When I import all dates are char variable except Program start date. I tried changing the other three to the date format similar to that of program start date in excel, but that did not work. I tried changing 3 character variable that appears as dates in sas to numeric date. That did not work either. It returns me the variables as missing. Any suggestions how to solve this?

This is data in Excel.

id	Program Start date	Program Close date	Session Code	Session Start date	Session end date
0000000101	3/16/2018		A	2018-03-26	2018-04-09
0000000102	3/16/2018		A	2018-04-09
0000000103	10/8/2013		A	2018-01-31	2018-05-30
0000000104	10/8/2013		A	2018-03-05	2018-05-30
0000000105	10/8/2013		A	2018-01-31	2018-05-30
0000000106	3/23/2018		A	2016-10-18
0000000107	3/23/2018		A	2018-03-23
0000000108	10/7/2017		B	2016-10-19
0000000109	10/7/2017		B	2016-10-19
0000000110	10/7/2017		B	2016-10-26
0000000111	12/1/2017		B	2017-04-28
0000000112	12/1/2017		B	2017-04-28
0000000113	4/2/2018		B	2017-12-11	2018-04-30
0000000114	4/2/2018		A	2017-12-11	2018-04-30
0000000115	3/3/2018		A	2018-03-13
0000000116	3/3/2018		A	2015-05-14
0000000117	3/3/2018		A	2018-03-19	2018-05-22
0000000118	1/17/2017		A	2017-01-18
0000000119	1/17/2017		A	2017-01-17
0000000120	8/14/2017		C	2017-12-18
0000000121	8/14/2017		C	2017-10-03
0000000122	12/19/2017	2018-05-31	C	2017-09-22
0000000123	12/19/2017	2018-05-31	C	2016-01-04
0000000124	12/19/2017	2018-05-31	C	2017-09-22
0000000125	3/4/2013		C	2017-08-28
0000000126	3/4/2013		C	2017-08-28
0000000127	3/4/2013		C	2013-02-14
0000000128	12/25/2017	2018-05-31	C	2017-11-13	2018-04-17
0000000129	12/25/2017	2018-05-31	C	2018-04-18

After Import contents looks like this.

Variable	Type	Len	Format	Informat
Session Start date	Char	10	$10.	$10.
Session Code	Char	2	$2.	$2.
Session end date	Char	10	$10.	$10.
Program Start date	Num	8	MMDDYY10.
Program Close date	Char	10	$10.	$10.

Data in SAS looks like this.

id	Program Start date	Program Close date	Session Code	Session Start date	Session end date
0000000101	3/16/2018		A	3/26/2018	4/9/2018
0000000102	3/16/2018		A	4/9/2018
0000000103	10/8/2013		A	1/31/2018	5/30/2018
0000000104	10/8/2013		A	3/5/2018	5/30/2018
0000000105	10/8/2013		A	1/31/2018	5/30/2018
0000000106	3/23/2018		A	10/18/2016
0000000107	3/23/2018		A	3/23/2018
0000000108	10/7/2017		B	10/19/2016
0000000109	10/7/2017		B	10/19/2016
0000000110	10/7/2017		B	10/26/2016
0000000111	12/1/2017		B	4/28/2017
0000000112	12/1/2017		B	4/28/2017
0000000113	4/2/2018		B	12/11/2017	4/30/2018
0000000114	4/2/2018		A	12/11/2017	4/30/2018
0000000115	3/3/2018		A	3/13/2018
0000000116	3/3/2018		A	5/14/2015
0000000117	3/3/2018		A	3/19/2018	5/22/2018
0000000118	1/17/2017		A	1/18/2017
0000000119	1/17/2017		A	1/17/2017
0000000120	8/14/2017		C	12/18/2017
0000000121	8/14/2017		C	10/3/2017
0000000122	12/19/2017	5/31/2017	C	9/22/2017
0000000123	12/19/2017	5/31/2018	C	1/4/2016
0000000124	12/19/2017	5/31/2018	C	9/22/2017
0000000125	3/4/2013		C	8/28/2017
0000000126	3/4/2013		C	8/28/2017
0000000127	3/4/2013		C	2/14/2013
0000000128	12/25/2017	5/31/2018	C	11/13/2017	4/17/2018
0000000129	12/25/2017	5/31/2018	C	4/18/2018

Code I used:

data work.dataA;

set work.data;

Session Start date2=input(Session Start date,$10.);

Session end date2=input(Session end date,$10.);

Program Close date2=input(Program Close date,$10.);

format Session Start date2 mmddyy10.;

format Session end date2 mmddyy10.;

format Program Close date2 mmddyy10.;

run;

Re: Converting character variable to dates , some blank dates

pau13rown — Thu, 28 Jun 2018 21:54:51 GMT

there are several things you could do. i wouldn't modify the csv file. You could save the sas code generated when you import the csv, and then modify the code ie to use a date format. Or use the substr function to convert to date eg something like:

format startdt date9.;

day=input(substr(startdate,9,2),2.);
month=input(substr(startdate,6,2),2.);
year=input(substr(startdate,1,4),4.);
if day ne . and month ne . and year ne .
then startdt=mdy(month,day,year);

Re: Converting character variable to dates , some blank dates

Reeza — Thu, 28 Jun 2018 21:54:19 GMT

You're pretty close!

SessionStartdate2=input(Session Start date,$10.);

The parameter in red, should be the informat - how the data currently appears and should be read in. Since it's yymmdd10 you should use that informat.
You cannot have spaces in variable names.

SessionStartdate2=input(Session Start date, yymmdd10.);
format sessionstartdate2 yymmdd10.;

@d0816 wrote:

I have following dataset in excel. Some of the close and end dates are blank because those events have not occurred. When I import all dates are char variable except Program start date. I tried changing the other three to the date format similar to that of program start date in excel, but that did not work. I tried changing 3 character variable that appears as dates in sas to numeric date. That did not work either. It returns me the variables as missing. Any suggestions how to solve this?

This is data in Excel.

id Program Start date Program Close date Session Code Session Start date Session end date

0000000101 3/16/2018 A 2018-03-26 2018-04-09

0000000102 3/16/2018 A 2018-04-09

0000000103 10/8/2013 A 2018-01-31 2018-05-30

0000000104 10/8/2013 A 2018-03-05 2018-05-30

0000000105 10/8/2013 A 2018-01-31 2018-05-30

0000000106 3/23/2018 A 2016-10-18

0000000107 3/23/2018 A 2018-03-23

0000000108 10/7/2017 B 2016-10-19

0000000109 10/7/2017 B 2016-10-19

0000000110 10/7/2017 B 2016-10-26

0000000111 12/1/2017 B 2017-04-28

0000000112 12/1/2017 B 2017-04-28

0000000113 4/2/2018 B 2017-12-11 2018-04-30

0000000114 4/2/2018 A 2017-12-11 2018-04-30

0000000115 3/3/2018 A 2018-03-13

0000000116 3/3/2018 A 2015-05-14

0000000117 3/3/2018 A 2018-03-19 2018-05-22

0000000118 1/17/2017 A 2017-01-18

0000000119 1/17/2017 A 2017-01-17

0000000120 8/14/2017 C 2017-12-18

0000000121 8/14/2017 C 2017-10-03

0000000122 12/19/2017 2018-05-31 C 2017-09-22

0000000123 12/19/2017 2018-05-31 C 2016-01-04

0000000124 12/19/2017 2018-05-31 C 2017-09-22

0000000125 3/4/2013 C 2017-08-28

0000000126 3/4/2013 C 2017-08-28

0000000127 3/4/2013 C 2013-02-14

0000000128 12/25/2017 2018-05-31 C 2017-11-13 2018-04-17

0000000129 12/25/2017 2018-05-31 C 2018-04-18

After Import contents looks like this.

Variable
Type Len Format Informat

Session Start date Char 10 $10. $10.

Session Code Char 2 $2. $2.

Session end date Char 10 $10. $10.

Program Start date Num 8 MMDDYY10.

Program Close date Char 10 $10. $10.

Data in SAS looks like this.

id Program Start date Program Close date Session Code Session Start date Session end date

0000000101 3/16/2018 A 3/26/2018 4/9/2018

0000000102 3/16/2018 A 4/9/2018

0000000103 10/8/2013 A 1/31/2018 5/30/2018

0000000104 10/8/2013 A 3/5/2018 5/30/2018

0000000105 10/8/2013 A 1/31/2018 5/30/2018

0000000106 3/23/2018 A 10/18/2016

0000000107 3/23/2018 A 3/23/2018

0000000108 10/7/2017 B 10/19/2016

0000000109 10/7/2017 B 10/19/2016

0000000110 10/7/2017 B 10/26/2016

0000000111 12/1/2017 B 4/28/2017

0000000112 12/1/2017 B 4/28/2017

0000000113 4/2/2018 B 12/11/2017 4/30/2018

0000000114 4/2/2018 A 12/11/2017 4/30/2018

0000000115 3/3/2018 A 3/13/2018

0000000116 3/3/2018 A 5/14/2015

0000000117 3/3/2018 A 3/19/2018 5/22/2018

0000000118 1/17/2017 A 1/18/2017

0000000119 1/17/2017 A 1/17/2017

0000000120 8/14/2017 C 12/18/2017

0000000121 8/14/2017 C 10/3/2017

0000000122 12/19/2017 5/31/2017 C 9/22/2017

0000000123 12/19/2017 5/31/2018 C 1/4/2016

0000000124 12/19/2017 5/31/2018 C 9/22/2017

0000000125 3/4/2013 C 8/28/2017

0000000126 3/4/2013 C 8/28/2017

0000000127 3/4/2013 C 2/14/2013

0000000128 12/25/2017 5/31/2018 C 11/13/2017 4/17/2018

0000000129 12/25/2017 5/31/2018 C 4/18/2018

Code I used:

data work.dataA;

set work.data;

Session Start date2=input(Session Start date,$10.);

Session end date2=input(Session end date,$10.);

Program Close date2=input(Program Close date,$10.);

format Session Start date2 mmddyy10.;

format Session end date2 mmddyy10.;

format Program Close date2 mmddyy10.;

run;

Re: Converting character variable to dates , some blank dates

d0816 — Fri, 29 Jun 2018 13:30:43 GMT

I tried the changes Reeza suggested and it worked.

Thanks Reeza and Paul for replying.