What am I missing in a data step to allow me to use substring to fill the year of a date?
proc sql; create table test1 (academic_period varchar2(6)); quit; proc sql; insert into test1 (academic_period) values ('199910'); quit; data test1; set test1; If SUBSTR(ACADEMIC_PERIOD,5,2) = '10' then somedate = input("10/15%str(SUBSTR(t1.ACADEMIC_PERIOD,1,4)))", mmddyy10.); run;
input(catt("10/15/", SUBSTR(ACADEMIC_PERIOD,1,4)), mmddyy10.)
Maybe something like that?
May also be worth looking at MDY() function if you want a SAS date?
Edit - remove the t1 reference since that's only good in SQL
@DavidPhillips2 wrote:
What am I missing in a data step to allow me to use substring to fill the year of a date?
proc sql; create table test1 (academic_period varchar2(6)); quit; proc sql; insert into test1 (academic_period) values ('199910'); quit; data test1; set test1; If SUBSTR(ACADEMIC_PERIOD,5,2) = '10' then somedate = input("10/15%str(SUBSTR(t1.ACADEMIC_PERIOD,1,4)))", mmddyy10.); run;
input(catt("10/15/", SUBSTR(ACADEMIC_PERIOD,1,4)), mmddyy10.)
Maybe something like that?
May also be worth looking at MDY() function if you want a SAS date?
Edit - remove the t1 reference since that's only good in SQL
@DavidPhillips2 wrote:
What am I missing in a data step to allow me to use substring to fill the year of a date?
proc sql; create table test1 (academic_period varchar2(6)); quit; proc sql; insert into test1 (academic_period) values ('199910'); quit; data test1; set test1; If SUBSTR(ACADEMIC_PERIOD,5,2) = '10' then somedate = input("10/15%str(SUBSTR(t1.ACADEMIC_PERIOD,1,4)))", mmddyy10.); run;
proc sql; create table test1 (academic_period varchar2(6)); quit; proc sql; insert into test1 (academic_period) values ('199910'); quit; data test1; set test1; format somedate mmddyy10.; If SUBSTR(ACADEMIC_PERIOD,5,2) = '10' then somedate = mdy(10, 15, SUBSTR(ACADEMIC_PERIOD,1,4)); run;
This worked.
I don't think the MMDDYY informat is going to like a date string that has the letters 'SUBS' in the location where it is expecting the 4 digits of the year.
data test1; set test1;
If SUBSTR(ACADEMIC_PERIOD,5,2) = '10' then
somedate = input(cats('10/15/',SUBSTR(ACADEMIC_PERIOD,1,4),mmddyy10.)
;
run;
Why not just use the YYMMN informat?
119 data x; 120 x = input('199910',yymmn6.); 121 put x= date9.; 122 run; x=01OCT1999
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.