topic Re: change export format of datetime data in SAS Programming

change export format of datetime data

BRKS2 — Mon, 29 Aug 2022 16:14:08 GMT

Hello,

I have data that I am exporting to a text file, and it contains a couple of datetime fields.

I do not want those exported using the typical SAS format (30JUN2022:15:23:34.760000).

Instead, I would like to have the 'date' portion in the YYYYMMDD format: (2022-06-30 15:23:34.760000)

How do I modify this code to get the correct output format?

Thanks!

Barb

proc export data=client.incrmtl

outfile = "&filepath/incrmtl.txt"

dbms=tab replace

;

putnames=yes;

run;

data _null_;

%let _EFIERR_ = 0;

%let _EFIREC_ = 0;

file '/sasem/gbmkuser/client/incrmtl_data.txt' delimiter='09'x DSD DROPOVER lrecl=32767;

if _n_ = 1 then

do;

put

"CRET_TS"

;

end;

set CLIENT.INCRMTL end=EFIEOD;

format CRET_TS datetime26.6 ;

do;

EFIOUT + 1;

put CRET_TS @;

;

end;

if _ERROR_ then call symputx('_EFIERR_',1);

if EFIEOD then call symputx('_EFIREC_',EFIOUT);

run;

Re: change export format of datetime data

Tom — Mon, 29 Aug 2022 16:28:18 GMT

Are you using PROC EXPORT to create the tab delimited file?

Or are you running your own data step to create the tab delimited file?

I don't think there is a SAS defined format that produces that exact style. But you could either create your own format using the PICTURE statement of PROC FORMAT.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p0n990vq8gxca6n1vnsracr6jp2c.htm

Then all you need to do is change the format you have attached to the variable that has the datetime value in it.

Or you could build your own character string and just write that instead. That is easier if you are running your own data step to write the text file.

data _null_;
  set client.incrmtl;
  file '/sasem/gbmkuser/client/incrmtl_data.txt' dsd dlm='09'x ;
  length dt_string $26 ;
  if _n_=1 then do;
    put "CRET_TS" @;
   put;
 end;
 dt_string=translate(put(cret_ts,E8601DT26.6),' ','T');
 put dt_string ;
run;

Re: change export format of datetime data

BRKS2 — Mon, 29 Aug 2022 16:39:52 GMT

Hi Tom!
In your reply, it looks like there is a format

E8601DT26.6

that would do what I need.

Is that correct?

Thanks so much,

Barb

Re: change export format of datetime data

Tom — Mon, 29 Aug 2022 16:44:39 GMT

As long as you don't mind using the ISO standard of having a capital letter T instead of space between the date part and time part.

Re: change export format of datetime data

BRKS2 — Mon, 29 Aug 2022 16:49:33 GMT

Woot! That is great - thank you so much!

I just have one follow up question...

I coded my own export as follows, but I'm only getting the first record in the export.

Can you tell me how to fix my make-shift code so that all of the records are exported?

Thanks so much - you are a lifesaver!

Barb

data _null_;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
%let _EFIREC_ = 0; /* clear export record count macro variable*/
file '/sasem/gbmkuser/client/incrmtl_ieid_98233475.txt' delimiter='09'x DSD lrecl=32767;
if _n_ = 1 then /* write column names or labels */
do;
put
"CURR_RCD_IND"
'09'x
"INDIV_ENTPR_ID"
'09'x
"CHNL_SRC_CD"
'09'x
"CLIENT_ID"
'09'x
"CLIENT_ACCT_NUM"
'09'x
"PRODT_CTGRY_CD"
'09'x
"CLIENT_BEN_KEY"
'09'x
"BEN_PLAN_NUM"
'09'x
"CUST_ELGBTY_CVRG_EFF_DT"
'09'x
"CUST_ELGBTY_CVRG_TERM_DT"
'09'x
"RCD_EFF_TS"
'09'x
"RCD_TERM_TS"
'09'x
"CUST_INTGRD_DTL_SV_EFF_DT"
'09'x
"CUST_INTGRD_DTL_SV_TERM_DT"
'09'x
"PRODT_FAM_CD"
'09'x
"PRODT_TY_CD"
'09'x
"CVRG_BEHVRAL_IND"
'09'x
"CVRG_DENT_IND"
'09'x
"CVRG_MED_IND"
'09'x
"CVRG_PHRM_IND"
'09'x
"CVRG_VSN_IND"
'09'x
"CVRG_EAP_IND"
'09'x
"CUST_INTGRD_DTL_INCRMTL_SV_KEY"
'09'x
"CRET_TS"
'09'x
"UPDT_TS"
;
end;
set CLIENT.IEID_98233475_INCRMTL end=EFIEOD;
format CURR_RCD_IND $1. ;
format INDIV_ENTPR_ID $40. ;
format CHNL_SRC_CD $10. ;
format CLIENT_ID $10. ;
format CLIENT_ACCT_NUM $10. ;
format PRODT_CTGRY_CD $30. ;
format CLIENT_BEN_KEY $32. ;
format BEN_PLAN_NUM $10. ;
format CUST_ELGBTY_CVRG_EFF_DT yymmdd10. ;
format CUST_ELGBTY_CVRG_TERM_DT yymmdd10. ;
format RCD_EFF_TS E8601DT26.6 ;
format RCD_TERM_TS E8601DT26.6 ;
format CUST_INTGRD_DTL_SV_EFF_DT yymmdd10. ;
format CUST_INTGRD_DTL_SV_TERM_DT yymmdd10. ;
format PRODT_FAM_CD $30. ;
format PRODT_TY_CD $30. ;
format CVRG_BEHVRAL_IND $1. ;
format CVRG_DENT_IND $1. ;
format CVRG_MED_IND $1. ;
format CVRG_PHRM_IND $1. ;
format CVRG_VSN_IND $1. ;
format CVRG_EAP_IND $1. ;
format CUST_INTGRD_DTL_INCRMTL_SV_KEY $32. ;
format CRET_TS E8601DT26.6 ;
format UPDT_TS E8601DT26.6 ;
do;
EFIOUT + 1;
put CURR_RCD_IND $ @;
put INDIV_ENTPR_ID $ @;
put CHNL_SRC_CD $ @;
put CLIENT_ID $ @;
put CLIENT_ACCT_NUM $ @;
put PRODT_CTGRY_CD $ @;
put CLIENT_BEN_KEY $ @;
put BEN_PLAN_NUM $ @;
put CUST_ELGBTY_CVRG_EFF_DT @;
put CUST_ELGBTY_CVRG_TERM_DT @;
put RCD_EFF_TS @;
put RCD_TERM_TS @;
put CUST_INTGRD_DTL_SV_EFF_DT @;
put CUST_INTGRD_DTL_SV_TERM_DT @;
put PRODT_FAM_CD $ @;
put PRODT_TY_CD $ @;
put CVRG_BEHVRAL_IND $ @;
put CVRG_DENT_IND $ @;
put CVRG_MED_IND $ @;
put CVRG_PHRM_IND $ @;
put CVRG_VSN_IND $ @;
put CVRG_EAP_IND $ @;
put CUST_INTGRD_DTL_INCRMTL_SV_KEY $ @;
put CRET_TS @;
put UPDT_TS @;
;
end;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

Re: change export format of datetime data

ballardw — Mon, 29 Aug 2022 16:58:57 GMT

You do not want the last variable to have the @ in the last put statement. That prevents any following output from appearing on the next line.

If you look at your output file you should see lots of data to right of the "first record" on the same line.

@BRKS2 wrote:

Woot! That is great - thank you so much!

I just have one follow up question...

I coded my own export as follows, but I'm only getting the first record in the export.

Can you tell me how to fix my make-shift code so that all of the records are exported?

Thanks so much - you are a lifesaver!

Barb

data _null_;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
%let _EFIREC_ = 0; /* clear export record count macro variable*/
file '/sasem/gbmkuser/client/incrmtl_ieid_98233475.txt' delimiter='09'x DSD lrecl=32767;
if _n_ = 1 then /* write column names or labels */
do;
put
"CURR_RCD_IND"
'09'x
"INDIV_ENTPR_ID"
'09'x
"CHNL_SRC_CD"
'09'x
"CLIENT_ID"
'09'x
"CLIENT_ACCT_NUM"
'09'x
"PRODT_CTGRY_CD"
'09'x
"CLIENT_BEN_KEY"
'09'x
"BEN_PLAN_NUM"
'09'x
"CUST_ELGBTY_CVRG_EFF_DT"
'09'x
"CUST_ELGBTY_CVRG_TERM_DT"
'09'x
"RCD_EFF_TS"
'09'x
"RCD_TERM_TS"
'09'x
"CUST_INTGRD_DTL_SV_EFF_DT"
'09'x
"CUST_INTGRD_DTL_SV_TERM_DT"
'09'x
"PRODT_FAM_CD"
'09'x
"PRODT_TY_CD"
'09'x
"CVRG_BEHVRAL_IND"
'09'x
"CVRG_DENT_IND"
'09'x
"CVRG_MED_IND"
'09'x
"CVRG_PHRM_IND"
'09'x
"CVRG_VSN_IND"
'09'x
"CVRG_EAP_IND"
'09'x
"CUST_INTGRD_DTL_INCRMTL_SV_KEY"
'09'x
"CRET_TS"
'09'x
"UPDT_TS"
;
end;
set CLIENT.IEID_98233475_INCRMTL end=EFIEOD;
format CURR_RCD_IND $1. ;
format INDIV_ENTPR_ID $40. ;
format CHNL_SRC_CD $10. ;
format CLIENT_ID $10. ;
format CLIENT_ACCT_NUM $10. ;
format PRODT_CTGRY_CD $30. ;
format CLIENT_BEN_KEY $32. ;
format BEN_PLAN_NUM $10. ;
format CUST_ELGBTY_CVRG_EFF_DT yymmdd10. ;
format CUST_ELGBTY_CVRG_TERM_DT yymmdd10. ;
format RCD_EFF_TS E8601DT26.6 ;
format RCD_TERM_TS E8601DT26.6 ;
format CUST_INTGRD_DTL_SV_EFF_DT yymmdd10. ;
format CUST_INTGRD_DTL_SV_TERM_DT yymmdd10. ;
format PRODT_FAM_CD $30. ;
format PRODT_TY_CD $30. ;
format CVRG_BEHVRAL_IND $1. ;
format CVRG_DENT_IND $1. ;
format CVRG_MED_IND $1. ;
format CVRG_PHRM_IND $1. ;
format CVRG_VSN_IND $1. ;
format CVRG_EAP_IND $1. ;
format CUST_INTGRD_DTL_INCRMTL_SV_KEY $32. ;
format CRET_TS E8601DT26.6 ;
format UPDT_TS E8601DT26.6 ;
do;
EFIOUT + 1;
put CURR_RCD_IND $ @;
put INDIV_ENTPR_ID $ @;
put CHNL_SRC_CD $ @;
put CLIENT_ID $ @;
put CLIENT_ACCT_NUM $ @;
put PRODT_CTGRY_CD $ @;
put CLIENT_BEN_KEY $ @;
put BEN_PLAN_NUM $ @;
put CUST_ELGBTY_CVRG_EFF_DT @;
put CUST_ELGBTY_CVRG_TERM_DT @;
put RCD_EFF_TS @;
put RCD_TERM_TS @;
put CUST_INTGRD_DTL_SV_EFF_DT @;
put CUST_INTGRD_DTL_SV_TERM_DT @;
put PRODT_FAM_CD $ @;
put PRODT_TY_CD $ @;
put CVRG_BEHVRAL_IND $ @;
put CVRG_DENT_IND $ @;
put CVRG_MED_IND $ @;
put CVRG_PHRM_IND $ @;
put CVRG_VSN_IND $ @;
put CVRG_EAP_IND $ @;
put CUST_INTGRD_DTL_INCRMTL_SV_KEY $ @;
put CRET_TS @;
put UPDT_TS @;
;
end;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

Re: change export format of datetime data

BRKS2 — Mon, 29 Aug 2022 17:02:06 GMT

Thank you, thank you, thank you!!!!! 🙂

Re: change export format of datetime data

Tom — Mon, 29 Aug 2022 20:33:58 GMT

Here is picture format for ymd hms with space instead of T between the two.

proc format; 
  picture  ymdhms (default=19)
    low-high='%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime)
    other = 'unknown'
  ;
run;

3251  data _null_;
3252    now=datetime();
3253    put now ymdhms26.6 ;
3254  run;

2022-08-29 16:29:57.804000