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;
You're pretty close!
SessionStartdate2=input(Session Start date,$10.);
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;
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);
You're pretty close!
SessionStartdate2=input(Session Start date,$10.);
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;
I tried the changes Reeza suggested and it worked.
Thanks Reeza and Paul for replying.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.