topic use of substr with date time data in SAS Procedures

use of substr with date time data

robm — Tue, 14 Jan 2014 05:35:16 GMT

how do I extract year out of the below data ...sbstr gives this error

ERROR: Function SUBSTR requires a character expression as argument 1.

D_O_ADMN_DT
25JAN1932:00:00:00.000
17NOV1971:00:00:00.000
29OCT1975:00:00:00.000
29OCT1975:00:00:00.000
04MAR1932:00:00:00.000

2 proc sql ;
3 SELECT substr(c.D_O_ADMN_DT,6,4) from sasdata.casedata c;
ERROR: Function SUBSTR requires a character expression as argument 1.

Re: use of substr with date time data

Patrick — Tue, 14 Jan 2014 06:25:16 GMT

A SAS datetime value is stored in a numeric variable and represented as the number of seconds since 1/1/1960. You then apply a SAS format to make this number human readable. The format as such does not change the internal value but how it's printed.

Below code illustrates the principle:

data have;
dt=datetime();
run;

proc sql;
select
dt format=best16.,
dt format=datetime21.,
dt format=dtyear4.,

/* applies the format, then stores the resulting string in the new variable "year" */
put(dt,dtyear4.) as year
from have
;
quit;

Re: use of substr with date time data

robm — Tue, 14 Jan 2014 07:34:23 GMT

shouldn't this work then?

4 proc sql ;

5 SELECT year(c.D_O_ADMN_DT) from sasdata.casedata c;

NOTE: Writing HTML Body file: sashtml.htm

NOTE: Invalid argument to function YEAR. Missing values may be generated

Re: use of substr with date time data

KarthikSrivasthav — Tue, 14 Jan 2014 10:42:29 GMT

Hi robm,

Year function will work to extract year from datetime/date variable:

data want;

format a datetime.;

a=0;

yr=year(a);

run;

I think u r variable might be in character format.Check once.

Cheers,

Karthik

Re: use of substr with date time data

CTorres — Tue, 14 Jan 2014 14:19:10 GMT

Karthik,

The argument of the Year function is a date (not a datetime) so the correct use of the function would be as follows:

data want;

format a datetime.;

a=datetime();

yr=year(datepart(a));

run;

Regards,

Re: use of substr with date time data

CTorres — Tue, 14 Jan 2014 14:31:52 GMT

Try this:

proc sql ;

SELECT year(datepart(c.D_O_ADMN_DT)) from sasdata.casedata c;

Regards,

Re: use of substr with date time data

robm — Wed, 15 Jan 2014 04:46:53 GMT

thanks CTorres

year(datepart(c.D_O_ADMN_DT))>=&startDate. and year(datepart(c.D_O_ADMN_DT))<=&endDate. then 'Y'

seems to give

ERROR: Character expression requires a character format.

967

968 /* Mark case records where the patient has a case between the start/endDates */

969 proc sql;

970 create table a.&ir._tab01 as

971 select p.P_ACTIVE_CLINIC,

972 p.P_PATIENT_NUMBER, c.C_CASE_NUMBER, p.P_D_O_LAST_UPDATE format yymmdd10. as patLastUpd,

973 p.P_D_O_DEATH format yymmdd10., c.C_D_O_ADMN format yymmdd10.,

974 p.P_POSTAL_CODE, c.C_POSTAL_CODE, c.C_RES_PROV_CODE, p.P_PROVINCE format = $provid.,

975 c.C_RESIDENCE_CODE,

976 substr(c.C_PATIENT_NUMBER,6,2) || substr(c.C_PATIENT_NUMBER,4,2) ||

976 ! substr(c.C_PATIENT_NUMBER,2,2) || substr(c.C_PATIENT_NUMBER,1,1) as sortseq,

977

978 case

979 when year(datepart(c.D_O_ADMN_DT))>=&startDate. and

979 ! year(datepart(c.D_O_ADMN_DT))<=&endDate. then 'Y'

980 else ' '

981 end as insideRange

982

983 from sasdata.casedata c, sasdata.patient p

984 where c.C_PATIENT_NUMBER = p.P_PATIENT_NUMBER

985 and c.C_ADMISSION_TYPE = '12'

986 ;

ERROR: Character expression requires a character format.

987 run;

hey that's great thanks Torres

Re: use of substr with date time data

TarunKumar — Wed, 15 Jan 2014 06:21:54 GMT

use san function

scan(do_admn_dt,1,': ')

Re: use of substr with date time data

Patrick — Wed, 15 Jan 2014 06:40:59 GMT

"scan()" is like "substr()" a string function and you would face the same issues as described in the initial post.

Re: use of substr with date time data

TarunKumar — Wed, 15 Jan 2014 07:01:26 GMT

scan(do_admn_dt,1,': ')

try this

do_admn_dt_year =input(substr(scan(compress(do_admn_dt),1,': '),6,4),9.2);

Re: use of substr with date time data

Patrick — Wed, 15 Jan 2014 08:17:31 GMT

You're missing that this is about extracting the year part out of a SAS datetime value - which is stored as a numeric value and not a character value. "Substr()", "scan()" and "compress()" are functions for character values dealing with strings.

To extract the year out of the numeric variable "dt" containing as SAS datetime value you can either use something like "year(datepart(dt))" which will return the year as a four digit numeric value, or you can use something like "put(dt,dtyear4.)" which will return the year as a four digit string (character value).

Re: use of substr with date time data

Peter_C — Wed, 15 Jan 2014 08:42:35 GMT

why does this seem so difficult?

If the original var is string then the substr() would NOT have caused the error message.

since the numeric value has the presentation style of a datetime value I assume the internal value is a datetime.

to create a string from a numeric like this we use a put() function. The function uses a format to choose the presentation style. The format to present just the year of a datetime value is DTYEAR.

YEAR_STRING= PUT( D_O_ADMN_DT, DTYEAR4. ) ;

Re: use of substr with date time data

Peter_C — Wed, 15 Jan 2014 08:47:28 GMT

sorry Patrick

you posted while I was searching for a DTYEAR function

Don't think my post adds anything.

regards

peter

Re: use of substr with date time data

CTorres — Wed, 15 Jan 2014 13:46:15 GMT

Three comments:

1. For your program to work correctly the macrovariables &startDate and &endDate should contain a 4 digit year, not a full date.

2. I think the expression "year(datepart(c.D_O_ADMN_DT))>=&startDate. and year(datepart(c.D_O_ADMN_DT))<=&endDate. then 'Y'" is not the one causing the three errors.

3. I would guess the formats you are applying to the variables "p.P_D_O_LAST_UPDATE format yymmdd10. as patLastUpd, p.P_D_O_DEATH format yymmdd10., c.C_D_O_ADMN format yymmdd10.," are those causing the errors. Check if they are character.

Good luck

Re: use of substr with date time data

robm — Wed, 15 Jan 2014 14:44:10 GMT

Hi C

yup &startDate and &endDate are a 4 digit year # assigned like this

%let startDate = 2007;

%let endDate = 2009 ;

here is what I get from

proc sql;

select p.P_D_O_LAST_UPDATE from sasdata.casedata c, sasdata.patient p

where c.C_PATIENT_NUMBER = p.P_PATIENT_NUMBER

and c.C_ADMISSION_TYPE ^= '12'

;

so it looks like a date

199511 8

199511 2

Re: use of substr with date time data

CTorres — Wed, 15 Jan 2014 15:09:50 GMT

Yes, they look like a date but Character or Numeric?

Try this to get the attributes of the variable .P_D_O_LAST_UPDATE :

proc sql ;

select name, type, length, label, format, informat

from sashelp.vcolumn

where libname = 'SASDATA'

and memname = 'PATIENT'

and name = 'P_D_O_LAST_UPDATE'

;

quit;

Regards,

Re: use of substr with date time data

Peter_C — Thu, 23 Jan 2014 11:31:27 GMT

When you see

year(datepart(c.D_O_ADMN_DT))>=&startDate. and

year(datepart(c.D_O_ADMN_DT))<=&endDate. then 'Y'

seems to give

ERROR: Character expression requires a character format.

try handling the year as a string:

put(c.D_O_ADMN_DT, dtyear4.)>="&startDate." and

put(c.D_O_ADMN_DT, dtyear4.)<="&endDate." then 'Y'

of course, you could half the process time for the put() conversion by changing your WHEN clause to:

when put(c.D_O_ADMN_DT, dtyear4.)

between "&startYEAR." and "&endYEAR." then 'Y'

this depends on &startYEAR and &endYEAR being 4digit year strings and column D_O_ADMN_DT being a standard SAS numeric datetime value

Message was edited by: Peter Crawford reviewing this - more questions surface ----->

why would you SUBSTR( patient_NUMBER (if that is a numeric number ) ?

if that is a number, you first need to convert it to a string with the usual

PUT( ) substr( PUT( PATIENT_NUMBER, {suitable format}

However, what format should be used?

Have all patient_number the same number of digits - should there be leading zeroes or should the conversion suppress leading zeroes?

Is there already a special format for patient_number?

Re: use of substr with date time data

robm — Sun, 26 Jan 2014 19:21:36 GMT

looks like its a string ....

P_D_O_LAST_UPDATE char 8 P_D_O_LAST_UPDATE $8. $8.

Re: use of substr with date time data

robm — Sun, 26 Jan 2014 19:29:37 GMT

now i get

ERROR: Character expression requires a character format.

ERROR: Expression using greater than or equal (>=) has components that are of different data types.

ERROR: Expression using less than or equal (<=) has components that are of different data types.

Re: use of substr with date time data

Tom — Sun, 26 Jan 2014 20:42:40 GMT

The problem is not with your datetime variable D_O_ADMN_DT, bu with the character variables P_D_O_LAST_UPDATE, P_D_O_DEATH and C_D_O_ADMN. The error message appears three times because you are attempting to attach the numeric format YYMMDD10. to these three character variables.

You could leave them as character and remove the "format yymmdd10." parts of the query and the errors will go away.

If you want to convert these character strings into actual dates then you will need to do a little work as SAS will not like the embedded blanks that you showed in the example values for P_D_O_LAST_UPDATE. To convert them to actual dates could do something like

input(translate(p.P_D_O_LAST_UPDATE,'0',' '),yymmdd8.) format yymmdd10. as patLastUpd,