DATA Step, Macro, Functions and more

Converting character variable to dates , some blank dates

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Converting character variable to dates , some blank dates

[ Edited ]

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.

idProgram Start dateProgram Close dateSession CodeSession Start dateSession end date
00000001013/16/2018 A2018-03-262018-04-09
00000001023/16/2018 A2018-04-09 
000000010310/8/2013 A2018-01-312018-05-30
000000010410/8/2013 A2018-03-052018-05-30
000000010510/8/2013 A2018-01-312018-05-30
00000001063/23/2018 A2016-10-18 
00000001073/23/2018 A2018-03-23 
000000010810/7/2017 B2016-10-19 
000000010910/7/2017 B2016-10-19 
000000011010/7/2017 B2016-10-26 
000000011112/1/2017 B2017-04-28 
000000011212/1/2017 B2017-04-28 
00000001134/2/2018 B2017-12-112018-04-30
00000001144/2/2018 A2017-12-112018-04-30
00000001153/3/2018 A2018-03-13 
00000001163/3/2018 A2015-05-14 
00000001173/3/2018 A2018-03-192018-05-22
00000001181/17/2017 A2017-01-18 
00000001191/17/2017 A2017-01-17 
00000001208/14/2017 C2017-12-18 
00000001218/14/2017 C2017-10-03 
000000012212/19/20172018-05-31C2017-09-22 
000000012312/19/20172018-05-31C2016-01-04 
000000012412/19/20172018-05-31C2017-09-22 
00000001253/4/2013 C2017-08-28 
00000001263/4/2013 C2017-08-28 
00000001273/4/2013 C2013-02-14 
000000012812/25/20172018-05-31C2017-11-132018-04-17
000000012912/25/20172018-05-31C2018-04-18 

 

 After Import contents looks like this.

Variable

TypeLenFormatInformat
Session Start dateChar10$10.$10.
Session CodeChar2$2.$2.
Session end dateChar10$10.$10.
Program Start dateNum8MMDDYY10. 
Program Close dateChar10$10.$10.

 

 

Data in SAS looks like this.

idProgram Start dateProgram Close dateSession CodeSession Start dateSession end date
00000001013/16/2018 A3/26/20184/9/2018
00000001023/16/2018 A4/9/2018 
000000010310/8/2013 A1/31/20185/30/2018
000000010410/8/2013 A3/5/20185/30/2018
000000010510/8/2013 A1/31/20185/30/2018
00000001063/23/2018 A10/18/2016 
00000001073/23/2018 A3/23/2018 
000000010810/7/2017 B10/19/2016 
000000010910/7/2017 B10/19/2016 
000000011010/7/2017 B10/26/2016 
000000011112/1/2017 B4/28/2017 
000000011212/1/2017 B4/28/2017 
00000001134/2/2018 B12/11/20174/30/2018
00000001144/2/2018 A12/11/20174/30/2018
00000001153/3/2018 A3/13/2018 
00000001163/3/2018 A5/14/2015 
00000001173/3/2018 A3/19/20185/22/2018
00000001181/17/2017 A1/18/2017 
00000001191/17/2017 A1/17/2017 
00000001208/14/2017 C12/18/2017 
00000001218/14/2017 C10/3/2017 
000000012212/19/20175/31/2017C9/22/2017 
000000012312/19/20175/31/2018C1/4/2016 
000000012412/19/20175/31/2018C9/22/2017 
00000001253/4/2013 C8/28/2017 
00000001263/4/2013 C8/28/2017 
00000001273/4/2013 C2/14/2013 
000000012812/25/20175/31/2018C11/13/20174/17/2018
000000012912/25/20175/31/2018C4/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;


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 23,700

Re: Converting character variable to dates , some blank dates

[ Edited ]

You're pretty close!

 

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

 

  1. 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. 
  2. 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;


 

View solution in original post


All Replies
Regular Contributor
Posts: 157

Re: Converting character variable to dates , some blank dates

[ Edited ]

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);

--------------
blog: papersandprograms.com
Solution
3 weeks ago
Super User
Posts: 23,700

Re: Converting character variable to dates , some blank dates

[ Edited ]

You're pretty close!

 

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

 

  1. 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. 
  2. 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;


 

Contributor
Posts: 38

Re: Converting character variable to dates , some blank dates

I tried the changes Reeza suggested and it worked.

 

Thanks Reeza and Paul for replying.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 82 views
  • 0 likes
  • 3 in conversation