cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
d0816
Quartz | Level 8 d0816
Quartz | Level 8
Go to Solution

Converting character variable to dates , some blank dates

Posted 06-28-2018 05:40 PM (3423 views)

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;

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User Reeza
Super User

Re: Converting character variable to dates , some blank dates

Posted 06-28-2018 05:54 PM (3458 views)  |   In reply to d0816

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

0 Likes
3 REPLIES 3
pau13rown
Lapis Lazuli | Level 10 pau13rown
Lapis Lazuli | Level 10

Re: Converting character variable to dates , some blank dates

Posted 06-28-2018 05:53 PM (3413 views)  |   In reply to d0816

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

0 Likes
Reeza
Super User Reeza
Super User

Re: Converting character variable to dates , some blank dates

Posted 06-28-2018 05:54 PM (3459 views)  |   In reply to d0816

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;

 

0 Likes
d0816
Quartz | Level 8 d0816
Quartz | Level 8

Re: Converting character variable to dates , some blank dates

Posted 06-29-2018 09:30 AM (3376 views)  |   In reply to Reeza

I tried the changes Reeza suggested and it worked.

 

Thanks Reeza and Paul for replying.

0 Likes

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • ‎06-28-2018 05:40 PM
  • 3424 views
  • 0 likes
  • 3 in conversation