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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.