Hi everyone,
I would like to:
• convert all date formats into mmddyy10.
• All of these variables are in numeric form
• For those cells without year value, I would like to add year 2020
Here's a preview of the data:
A | B | C | D | E | F | G | H | I | J | K | L |
3/20/20 | 4/2/20 | 3/21/2020 | 4/1/2020 | 8-Apr | 12-Aug | 10-Jun | 10-Jun | 12-Aug | 4-Sep | 8/12/2020 | 12-Aug |
4/7/20 | 5/1/20 | 4/10/2020 | 4/24/2020 | 9-Jun | 8-Jun | 19-Oct | 21-Oct | 1/15/2021 | 15-Jan | ||
4/6/20 | 5/11/20 | 4/6/2020 | 5/3/2020 | 25-Jun | 1-Oct | ||||||
3/16/20 | 4/16/20 | 3/17/2020 | 4/8/2020 | 6-May | 11-Aug | 17-Jun | 11-Aug | ||||
3/27/20 | 4/12/20 | 3/30/2020 | 4/10/2020 | 1-May | 19-Jun | 19-May | 19-May | 18-May | |||
3/20/20 | 4/13/20 | 3/25/2020 | 4/10/2020 | 2-May | 13-Nov | 2-Jun | 2-Jun | 4-Aug | 3-Sep | 8/4/2020 | 4-Aug |
3/27/20 | 4/16/20 | 3/27/2020 | 4/10/2020 | 1-May | 28-Jul | 25-Jul | 29-May | 27-Jul | 7/28/2020 | ||
3/26/20 | 4/11/20 | 3/30/2020 | 4/9/2020 | 7-May | 2-Jun | 21-Jul | 21-Jul | 21-Jul | 10-Sep | ||
4/14/20 | 5/10/20 | 4/16/2020 | 5/6/2020 | 12-Jun | 26-Aug | ||||||
3/18/20 | 4/18/20 | 3/22/2020 | 4/5/2020 | 16-May | 20-Oct | 28-Jul | 10/27/2020 | 20-Oct | 22-Sep | 10/27/2020 | 10/27/2020 |
4/18/20 | 7/5/20 | 4/19/2020 | 6/15/2020 | 10-Aug | 8/21/2021 | 23-Dec | |||||
4/24/20 | 6/28/20 | 4/29/2020 | 6/15/2020 | 7/9/2020 | 4-Sep | 3-Aug | 18-Jul | 3/11/2021 | |||
3/25/20 | 4/12/20 | 3/29/2020 | 4/8/2020 | 5-May | 4-Aug | 4-Aug | 4-Aug | 4-Aug | 11/5/2020 |
I have attached the data below. I would appreciate all the help. Thanks in advance!
If you already have a SAS dataset you can use the FMTINFO() function to find which of the variables are dates so help automate changing the format attached to them.
proc import datafile='c:\downloads\dates.xlsx' dbms=xlsx out=dates replace;
run;
proc contents noprint data=dates out=contents;
run;
proc sql noprint;
select nliteral(name) into :dates separated by ' '
from contents
where 'date'=fmtinfo(format,'cat')
;
%let ndates=&sqlobs;
quit;
%if (&ndates) %then %do;
proc datasets nolist lib=work;
modify dates;
format &dates date9.;
run;
quit;
%end;
proc print data=dates(obs=5);
run;
Results
Obs A B C D E F G H I J K L 1 20MAR2020 02APR2020 21MAR2020 01APR2020 08APR2022 12AUG2022 10JUN2022 10JUN2022 12AUG2022 04SEP2022 12AUG2020 12AUG2022 2 07APR2020 01MAY2020 10APR2020 24APR2020 09JUN2022 08JUN2022 19OCT2022 21OCT2022 . . 15JAN2021 15JAN2022 3 06APR2020 11MAY2020 06APR2020 03MAY2020 25JUN2022 . . 01OCT2022 . . . . 4 16MAR2020 16APR2020 17MAR2020 08APR2020 06MAY2022 11AUG2022 . 17JUN2022 11AUG2022 . . . 5 27MAR2020 12APR2020 30MAR2020 10APR2020 01MAY2022 19JUN2022 19MAY2022 19MAY2022 18MAY2022 . . .
Here is how this FORUM displays the file you attached.
That sheet is going to cause trouble for importing into SAS as it appears to have at least one extra row above the actual place where the tabular data layout starts. But assuming you use the right code to import just the data table then only C and D look like they contain text. The other fields look like they contain dates and should be imported properly as SAS date values. The ones that do not appear to have a year are right aligned so they are probably numeric values so the year is there, it is just not being displayed on the screen. Once you have SAS date values you can attach any format that knows how to display date values, so let's use DATE9. in our example code.
format A B E--L date9.;
To convert C and D into dates you will need to use the INPUT() function to make new numeric variables. They appear to be using M-D-Y order in those strings so use the MMDDYY informat.
c_date = input(c,mmddyy10.);
d_date = input(d,mmddyy10.);
format c_date d_date date9.;
So that version does not have any issues. PROC IMPORT makes everything a date.
The CONTENTS Procedure Alphabetic List of Variables and Attributes # Variable Type Len Format Label 1 A Num 8 MMDDYY8. A 2 B Num 8 MMDDYY8. B 3 C Num 8 MMDDYY8. C 4 D Num 8 MMDDYY8. D 5 E Num 8 DATE5. E 6 F Num 8 DATE5. F 7 G Num 8 DATE5. G 8 H Num 8 DATE5. H 9 I Num 8 DATE5. I 10 J Num 8 DATE5. J 11 K Num 8 MMDDYY8. K 12 L Num 8 DATE5. L
Use whatever date type format you want with any of those variables. I would avoid either MMDDYY or DDMMYY as either choice will confuse half of your audience. Use DATE9. or YYMMDD10. .
Looks like they are all EXCEL dates.
A B C D E F G H I J K L 43910 43923 43911 43922 43929 44055 43992 43992 44055 44078 44055 44055 43928 43952 43931 43945 43991 43990 44123 44125 44211 44211 43927 43962 43927 43954 44007 44105 43906 43937 43907 43929 43957 44054 43999 44054 43917 43933 43920 43931 43952 44001 43970 43970 43969 43910 43934 43915 43931 43953 44148 43984 43984 44047 44077 44047 44047 43917 43937 43917 43931 43952 44040 44037 43980 44039 44040 43916 43932 43920 43930 43958 43984 44033 44033 44033 44084 43935 43961 43937 43957 43994 44434 43908 43939 43912 43926 43967 44124 44040 44131 44124 44096 44131 44131 43939 44017 43940 43997 44053 44429 44553 43945 44010 43950 43997 44021 44078 44046 44030 44266 43915 43933 43919 43929 43956 44047 44047 44047 44047 44140
A | B | C | D | E | F | G | H | I | J | K | L |
43910 | 43923 | 43911 | 43922 | 43929 | 44055 | 43992 | 43992 | 44055 | 44078 | 44055 | 44055 |
43928 | 43952 | 43931 | 43945 | 43991 | 43990 | 44123 | 44125 | 44211 | 44211 | ||
43927 | 43962 | 43927 | 43954 | 44007 | 44105 | ||||||
43906 | 43937 | 43907 | 43929 | 43957 | 44054 | 43999 | 44054 | ||||
43917 | 43933 | 43920 | 43931 | 43952 | 44001 | 43970 | 43970 | 43969 | |||
43910 | 43934 | 43915 | 43931 | 43953 | 44148 | 43984 | 43984 | 44047 | 44077 | 44047 | 44047 |
43917 | 43937 | 43917 | 43931 | 43952 | 44040 | 44037 | 43980 | 44039 | 44040 | ||
43916 | 43932 | 43920 | 43930 | 43958 | 43984 | 44033 | 44033 | 44033 | 44084 | ||
43935 | 43961 | 43937 | 43957 | 43994 | 44434 | ||||||
43908 | 43939 | 43912 | 43926 | 43967 | 44124 | 44040 | 44131 | 44124 | 44096 | 44131 | 44131 |
43939 | 44017 | 43940 | 43997 | 44053 | 44429 | 44553 | |||||
43945 | 44010 | 43950 | 43997 | 44021 | 44078 | 44046 | 44030 | 44266 | |||
43915 | 43933 | 43919 | 43929 | 43956 | 44047 | 44047 | 44047 | 44047 | 44140 |
If you already have a SAS dataset you can use the FMTINFO() function to find which of the variables are dates so help automate changing the format attached to them.
proc import datafile='c:\downloads\dates.xlsx' dbms=xlsx out=dates replace;
run;
proc contents noprint data=dates out=contents;
run;
proc sql noprint;
select nliteral(name) into :dates separated by ' '
from contents
where 'date'=fmtinfo(format,'cat')
;
%let ndates=&sqlobs;
quit;
%if (&ndates) %then %do;
proc datasets nolist lib=work;
modify dates;
format &dates date9.;
run;
quit;
%end;
proc print data=dates(obs=5);
run;
Results
Obs A B C D E F G H I J K L 1 20MAR2020 02APR2020 21MAR2020 01APR2020 08APR2022 12AUG2022 10JUN2022 10JUN2022 12AUG2022 04SEP2022 12AUG2020 12AUG2022 2 07APR2020 01MAY2020 10APR2020 24APR2020 09JUN2022 08JUN2022 19OCT2022 21OCT2022 . . 15JAN2021 15JAN2022 3 06APR2020 11MAY2020 06APR2020 03MAY2020 25JUN2022 . . 01OCT2022 . . . . 4 16MAR2020 16APR2020 17MAR2020 08APR2020 06MAY2022 11AUG2022 . 17JUN2022 11AUG2022 . . . 5 27MAR2020 12APR2020 30MAR2020 10APR2020 01MAY2022 19JUN2022 19MAY2022 19MAY2022 18MAY2022 . . .
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.