COMMUNITIES

Select Your Region

Visit the Cary, NC, US corporate headquarters site

Americas

Europe

Middle East & Africa

Asia Pacific

View our worldwide contacts list for help finding your region

Americas

Europe

Middle East & Africa

Asia Pacific

Sign In

Get access to My SAS, trials, communities and more.

Sign Out

Get access to My SAS, trials, communities and more.

SAS Sites

sas.com
Support
Blogs
Communities
Developer
Curiosity
Videos

Documentation

Learn
Brand
PartnerNet
Merchandise

SAS Programming

DATA Step, Macro, Functions and more
cancel
Turn on suggestions
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 (4554 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 (4589 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 (4544 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 (4590 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 (4507 views)  |   In reply to Reeza

I tried the changes Reeza suggested and it worked.

 

Thanks Reeza and Paul for replying.

0 Likes

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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