convert date yymmdd10. to ISO8601da.

[shyamtawde]

Dear Mates,

need to display format as iso8601dt instead of iso8601dt10.

below is the screenshot and code.

data ex3;
format AP01SDT1 AP02SDT1 AP03SDT1 AP04SDT1 yymmdd10.
AP01EDT1 AP02EDT1 AP03EDT1 AP04EDT1 yymmdd10.;
merge trans dmds ;
by usubjid;
/*deriving TRT01P,TRT02P*/
TRT01A=substr(t1,1,1);
TRT02A=substr(t2,1,1);
TRT03A=substr(t3,1,1);
TRT04A=substr(t4,1,1);
/*deriving TRT01P,TRT02P*/
TRT01P=substr(ARMCD,1,1);
TRT02P=substr(ARMCD,3,1);
TRT03P=substr(ARMCD,5,1);
TRT04P=substr(ARMCD,7,1);

/*period start date*/
AP01SDT1=input(AP01SDT,yymmdd10.);
if AP02SDT ne "" then AP02SDT1=input(AP02SDT,yymmdd10.);
if AP03SDT ne "" then AP03SDT1=input(AP03SDT,yymmdd10.);
if AP04SDT ne "" then AP04SDT1=input(AP04SDT,yymmdd10.);

/*period end date*/
AP01EDT1=input(AP01EDT,yymmdd10.);
if AP02EDT ne "" then AP02EDT1=input(AP02EDT,yymmdd10.);
if AP03EDT ne "" then AP03EDT1=input(AP03EDT,yymmdd10.);
if AP04EDT ne "" then AP04EDT1=input(AP03EDT,yymmdd10.);

/********SEQ for PLANNED and ACTUAL ARM*******;*/

TRTSEQP=ARMCD;
TRTSEQA=ACTARMCD;

if strip(Upcase(race))='INDIAN' then RACEN=1;
SEXN=ifn(strip(lowcase(sex))='m',1,2,.);
if strip(lowcase(ethnic))='not hispanic or latino' then ETHNICN=1;

drop armcd actarmcd;
run;

/******creating timing variable******/

data ex4 (rename = (TR01EDTM1=TR01EDTM TR02EDTM1=TR02EDTM TR03EDTM1=TR03EDTM TR04EDTM1=TR04EDTM
TR01SDTM1=TR01SDTM TR02SDTM1=TR02SDTM TR03SDTM1=TR03SDTM TR04SDTM1=TR04SDTM
AP01SDT1=AP01SDT AP02SDT1=AP02SDT AP03SDT1=AP03SDT AP04SDT1=AP04SDT
AP01EDT1=AP01EDT AP02EDT1=AP02EDT AP03EDT1=AP03EDT AP04EDT1=AP04EDT));
format TR01EDTM1 TR02EDTM1 TR03EDTM1 TR04EDTM1
TR01SDTM1 TR02SDTM1 TR03SDTM1 TR04SDTM1 datetime16.;

length trt01p trt02p trt03p trt04p trt01a trt02a trt03a trt04a $60;

set ex3;
If STUDYID=' ' then delete;
/*Deriving Analysis period start date,time*/
TR01SDT=input(scanq(p1,1,'T'),yymmdd10.);
TR02SDT=input(scanq(p2,1,'T'),yymmdd10.);
TR03SDT=input(scanq(p3,1,'T'),yymmdd10.);
if p4 ne "" then TR04SDT=input(scanq(p4,1,'T'),yymmdd10.);

TR01STM=input(scanq(p1,2,'T'),time5.);
TR02STM=input(scanq(p2,2,'T'),time5.);
TR03STM=input(scanq(p3,2,'T'),time5.);
TR04STM=input(scanq(p4,2,'T'),time5.);

TRTSDT=input(SCAN(RFSTDTC,1,'T'),yymmdd10.);
TRTSTM=input(SCAN(RFSTDTC,2,'T'),time5.);
TRTEDT=input(SCAN(RFENDTC,1,'T'),yymmdd10.);
TRTETM=input(SCAN(RFENDTC,2,'T'),time5.);
dthdt=input(scan(dthdtc,1,'T'),yymmdd10.);

/*Deriving Analysis period start date,time*/
TR01EDT=TR01SDT;
TR02EDT=TR02SDT;
TR03EDT=TR03SDT;
TR04EDT=TR04SDT;

TR01ETM=TR01STM;
TR02ETM=TR02STM;
TR03ETM=TR03STM;
TR04ETM=TR04STM;

format TR01SDT TR02SDT TR03SDT TR04SDT yymmdd10. TR01STM TR02STM TR03STM TR04STM time5.
TRTSDT TRTEDT TR01EDT TR02EDT TR03EDT TR04EDT yymmdd10. TRTSTM TRTETM TR01ETM TR02ETM TR03ETM TR04ETM time5.;

/*deriving population flag's*/
if TR01SDT ne . then do;
FASFL='Y';
SAFFL='Y';
RANDFL ='Y';
ENRLFL='Y';
end;

/*Deriving Analysis period end datetime*/
TR01EDTM1=input(TR01EDTM,is8601dt.);
if TR02EDTM ne "" then TR02EDTM1=input(TR02EDTM,is8601dt.);
if TR03EDTM ne "" then TR03EDTM1=input(TR03EDTM,is8601dt.);
if TR04EDTM ne "" then TR04EDTM1=input(TR04EDTM,is8601dt.);

/*Deriving Analysis period start datetime*/
if TR01SDTM ne "" then TR01SDTM1=input(TR01SDTM,is8601dt.);
if TR02SDTM ne "" then TR02SDTM1=input(TR02SDTM,is8601dt.);
if TR03SDTM ne "" then TR03SDTM1=input(TR03SDTM,is8601dt.);
if TR04SDTM ne "" then TR04SDTM1=input(TR04SDTM,is8601dt.);

if TRT01P='T' then TRT01PN=1;
ELSE if TRT01P='R' then TRT01PN=2;

if TRT02P='T' then TRT02PN=1;
ELSE if TRT02P='R' then TRT02PN=2;

if TRT03P='T' then TRT03PN=1;
ELSE if TRT03P='R' then TRT03PN=2;

if TRT04P='T' then TRT04PN=1;
ELSE if TRT04P='R' then TRT04PN=2;

if TRT01A='T' then TRT01AN=1;
ELSE if TRT01A='R' then TRT01AN=2;

if TRT02A='T' then TRT02AN=1;
ELSE if TRT02A='R' then TRT02AN=2;

if TRT03A='T' then TRT03AN=1;
ELSE if TRT03A='R' then TRT03AN=2;

if TRT04A='T' then TRT04AN=1;
ELSE if TRT04A='R' then TRT04AN=2;

/********ACTUAL ARM*******;*/
if TRT01A='R' then do;
TRT01A="Reference";end;
else if TRT01A='T' then do;
TRT01A="Test";end;

if TRT02A='R' then do;
TRT02A="Reference";end;
else if TRT02A='T' then do;
TRT02A="Test";end;

if TRT03A='R' then do;
TRT03A="Reference";end;
else if TRT03A='T' then do;
TRT03A="Test";end;

if TRT04A='R' then do;
TRT04A="Reference";end;
else if TRT04A='T' then do;
TRT04A="Test";end;

/********PLANNED ARM*******;*/

if TRT01P='R' then do;
TRT01P="Reference";end;
else if TRT01P='T' then do;
TRT01P="Test";end;

if TRT02P='R' then do;
TRT02P="Reference";end;
else if TRT02P='T' then do;
TRT02P="Test";end;

if TRT03P='R' then do;
TRT03P="Reference";end;
else if TRT03P='T' then do;
TRT03P="Test";end;

if TRT04P='R' then do;
TRT04P="Reference";end;
else if TRT04P='T' then do;
TRT04P="Test";end;

format dthdt date9.;


TRT01SDTM=P1;
drop p1 p2 P3 P4 t1 t2 T3 T4 TR01EDTM TR02EDTM TR03EDTM TR04EDTM;
run;

[Kurt_Bremser]

The ISO 8601 date format is equivalent to YYMMDD. E8601DA is equivalent to YYMMDD10., while B8601DA. is equivalent to YYMMDDN8.

[yabwon]

I'm not sure if you're aware that (her is the quite from SAS documentation): 

"E8601DAw. Format: Writes date values by using the ISO 8601 extended notation yyyy-mm-dd."

(https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/n1xn1ozuh3bpo6n180mtyrnssslh.ht...)

Formats yymmdd10. and E8601DA. are equivalent.

Bart

[shyamtawde]

Thank you both for your valuable time, but as per requirement from regulatory date, time and datetime format should be iso8601da, iso8601tm and iso8601dt respectively and my output showing iso8601da10., iso8601tm8., and iso8601dt19. which is not acceptable.

Please suggest me if any correction in code.

Thank you in advance!!!

[Kurt_Bremser]

These formats are, per definition, the same, so there is no need to change them.

[yabwon]

1) there are no such formats named: iso8601da, iso8601tm and iso8601dt in SAS, 

2) please share the text of the requirement, because from what I remember it was "value should be displayed as ISO",

3) therefore, maybe you need to have that variables as character variables formated in according ISO format? 

Bart

[Kurt_Bremser]

BTW, if you took the time to study the documentation (Maxim 1!), you would find that ISO8601 is an alias for E8601. Internally, the formats will always be named E8601.

[shyamtawde]

There are format errors in ADaM dataset  from Pinnacle 21 validation report that I want to resolve 

below the code I have submitted 
/******creating required variable as per adam guidelines******/

data ex3;
format AP01SDT1 AP02SDT1 AP03SDT1 AP04SDT1 IS8601DA.
AP01EDT1 AP02EDT1 AP03EDT1 AP04EDT1 IS8601DA.;
merge trans dmds ;
by usubjid;
/*deriving TRT01P,TRT02P*/
TRT01A=substr(t1,1,1);
TRT02A=substr(t2,1,1);
TRT03A=substr(t3,1,1);
TRT04A=substr(t4,1,1);
/*deriving TRT01P,TRT02P*/
TRT01P=substr(ARMCD,1,1);
TRT02P=substr(ARMCD,3,1);
TRT03P=substr(ARMCD,5,1);
TRT04P=substr(ARMCD,7,1);

/*period start date*/
if AP01SDT ne "" then AP01SDT1=input(strip(AP01SDT),IS8601DA.);
if AP02SDT ne "" then AP02SDT1=input(strip(AP02SDT),IS8601DA.);
if AP03SDT ne "" then AP03SDT1=input(strip(AP03SDT),IS8601DA.);
if AP04SDT ne "" then AP04SDT1=input(strip(AP04SDT),IS8601DA.);

/*period end date*/
if AP01EDT ne "" then AP01EDT1=input(strip(AP01EDT),IS8601DA.);
if AP02EDT ne "" then AP02EDT1=input(strip(AP02EDT),IS8601DA.);
if AP03EDT ne "" then AP03EDT1=input(strip(AP03EDT),IS8601DA.);
if AP04EDT ne "" then AP04EDT1=input(strip(AP04EDT),IS8601DA.);

/********SEQ for PLANNED and ACTUAL ARM*******;*/

TRTSEQP=ARMCD;
TRTSEQA=ACTARMCD;

if strip(Upcase(race))='INDIAN' then RACEN=1;
SEXN=ifn(strip(lowcase(sex))='m',1,2,.);
if strip(lowcase(ethnic))='not hispanic or latino' then ETHNICN=1;

drop armcd actarmcd;
run;

/******creating timing variable******/

data ex4 (rename = (TR01EDTM1=TR01EDTM TR02EDTM1=TR02EDTM TR03EDTM1=TR03EDTM TR04EDTM1=TR04EDTM
TR01SDTM1=TR01SDTM TR02SDTM1=TR02SDTM TR03SDTM1=TR03SDTM TR04SDTM1=TR04SDTM
AP01SDT1=AP01SDT AP02SDT1=AP02SDT AP03SDT1=AP03SDT AP04SDT1=AP04SDT
AP01EDT1=AP01EDT AP02EDT1=AP02EDT AP03EDT1=AP03EDT AP04EDT1=AP04EDT));
format TR01EDTM1 TR02EDTM1 TR03EDTM1 TR04EDTM1
TR01SDTM1 TR02SDTM1 TR03SDTM1 TR04SDTM1 datetime16.;

length trt01p trt02p trt03p trt04p trt01a trt02a trt03a trt04a $60;

set ex3;
If STUDYID=' ' then delete;
/*Deriving Analysis period start date,time*/
TR01SDT=input(scanq(p1,1,'T'),IS8601DA.);
TR02SDT=input(scanq(p2,1,'T'),IS8601DA.);
TR03SDT=input(scanq(p3,1,'T'),IS8601DA.);
if p4 ne "" then TR04SDT=input(scanq(p4,1,'T'),IS8601DA.);

TR01STM=input(scanq(p1,2,'T'),time5.);
TR02STM=input(scanq(p2,2,'T'),time5.);
TR03STM=input(scanq(p3,2,'T'),time5.);
TR04STM=input(scanq(p4,2,'T'),time5.);

TRTSDT=input(SCAN(RFSTDTC,1,'T'),IS8601DA.);
TRTSTM=input(SCAN(RFSTDTC,2,'T'),time5.);
TRTEDT=input(SCAN(RFENDTC,1,'T'),IS8601DA.);
TRTETM=input(SCAN(RFENDTC,2,'T'),time5.);

/*Deriving Analysis period start date,time*/
TR01EDT=TR01SDT;
TR02EDT=TR02SDT;
TR03EDT=TR03SDT;
TR04EDT=TR04SDT;

TR01ETM=TR01STM;
TR02ETM=TR02STM;
TR03ETM=TR03STM;
TR04ETM=TR04STM;

format TR01SDT TR02SDT TR03SDT TR04SDT IS8601DA. TR01STM TR02STM TR03STM TR04STM time5.
TRTSDT TRTEDT TR01EDT TR02EDT TR03EDT TR04EDT IS8601DA. TRTSTM TRTETM TR01ETM TR02ETM TR03ETM TR04ETM time5.;

/*deriving population flag's*/
if TR01SDT ne . then do;
FASFL='Y';
SAFFL='Y';
RANDFL ='Y';
ENRLFL='Y';
end;

/*Deriving Analysis period end datetime*/
TR01EDTM1=input(strip(TR01EDTM),is8601dt.);
if TR02EDTM ne "" then TR02EDTM1=input(strip(TR02EDTM),is8601dt.);
if TR03EDTM ne "" then TR03EDTM1=input(strip(TR03EDTM),is8601dt.);
if TR04EDTM ne "" then TR04EDTM1=input(strip(TR04EDTM),is8601dt.);

/*Deriving Analysis period start datetime*/
if TR01SDTM ne "" then TR01SDTM1=input(strip(TR01SDTM),is8601dt.);
if TR02SDTM ne "" then TR02SDTM1=input(strip(TR02SDTM),is8601dt.);
if TR03SDTM ne "" then TR03SDTM1=input(strip(TR03SDTM),is8601dt.);
if TR04SDTM ne "" then TR04SDTM1=input(strip(TR04SDTM),is8601dt.);

if TRT01P='T' then TRT01PN=1;
ELSE if TRT01P='R' then TRT01PN=2;

if TRT02P='T' then TRT02PN=1;
ELSE if TRT02P='R' then TRT02PN=2;

if TRT03P='T' then TRT03PN=1;
ELSE if TRT03P='R' then TRT03PN=2;

if TRT04P='T' then TRT04PN=1;
ELSE if TRT04P='R' then TRT04PN=2;

if TRT01A='T' then TRT01AN=1;
ELSE if TRT01A='R' then TRT01AN=2;

if TRT02A='T' then TRT02AN=1;
ELSE if TRT02A='R' then TRT02AN=2;

if TRT03A='T' then TRT03AN=1;
ELSE if TRT03A='R' then TRT03AN=2;

if TRT04A='T' then TRT04AN=1;
ELSE if TRT04A='R' then TRT04AN=2;

/********ACTUAL ARM*******;*/
if TRT01A='R' then do;
TRT01A="Reference";end;
else if TRT01A='T' then do;
TRT01A="Test";end;

if TRT02A='R' then do;
TRT02A="Reference";end;
else if TRT02A='T' then do;
TRT02A="Test";end;

if TRT03A='R' then do;
TRT03A="Reference";end;
else if TRT03A='T' then do;
TRT03A="Test";end;

if TRT04A='R' then do;
TRT04A="Reference";end;
else if TRT04A='T' then do;
TRT04A="Test";end;

/********PLANNED ARM*******;*/

if TRT01P='R' then do;
TRT01P="Reference";end;
else if TRT01P='T' then do;
TRT01P="Test";end;

if TRT02P='R' then do;
TRT02P="Reference";end;
else if TRT02P='T' then do;
TRT02P="Test";end;

if TRT03P='R' then do;
TRT03P="Reference";end;
else if TRT03P='T' then do;
TRT03P="Test";end;

if TRT04P='R' then do;
TRT04P="Reference";end;
else if TRT04P='T' then do;
TRT04P="Test";end;

TRT01SDTM=P1;
drop p1 p2 P3 P4 t1 t2 T3 T4 TR01EDTM TR02EDTM TR03EDTM TR04EDTM;
run;

Output like this 

[Kurt_Bremser]

A "validation" program that does not know that the valid SAS format for ISO 8601 is either E8601xx or B8601xx is useless in the first place.

To reassign a format, you only need a FORMAT statement, either in a DATA step or PROC DATASETS, or the FORMAT= option in PROC SQL.

If your input dataset actually contains date values in character variables, we need to see those first, so we can show you how to quickly convert them to SAS dates. Usually, such issues need to be fixed in the ETL code which brings data into SAS.

[ballardw]

---

@shyamtawde wrote:

Dear Mates,

need to display format as iso8601dt instead of iso8601dt10.

below is the screenshot and code.

---

Suggestion: Instead of complaining or showing a wall full of code show

1) ONE example value that you currently have that you do not like and

2) An example of that value should look like.

Almost certainly the only change is going to be the format. No reason to do a bunch of ugly and frankly moderately poor code.