topic Re: Substring to fill date in SAS Programming

Substring to fill date

DavidPhillips2 — Tue, 17 Mar 2020 21:00:58 GMT

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;

Re: Substring to fill date

Reeza — Tue, 17 Mar 2020 21:04:25 GMT

 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;

Re: Substring to fill date

DavidPhillips2 — Tue, 17 Mar 2020 21:06:31 GMT

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.

Re: Substring to fill date

Tom — Tue, 17 Mar 2020 21:07:37 GMT

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;

Re: Substring to fill date

Tom — Tue, 17 Mar 2020 21:11:31 GMT

Why not just use the YYMMN informat?

119   data x;
120     x = input('199910',yymmn6.);
121     put x= date9.;
122   run;

x=01OCT1999

Re: Substring to fill date

Reeza — Tue, 17 Mar 2020 21:32:49 GMT

Depending on what you need to scale to, may be better to go to MDY() function using period as month and day as 15 and year from academic period.