topic converting multiple character date into numeric in SAS Programming

converting multiple character date into numeric

Dhana18 — Wed, 06 Feb 2019 16:55:11 GMT

Hello,

SAS community support is a great resource for me. Than you all.

I need to convert multiple date variables into numeric and this is what I did. Is this right and is there a better efficient way to do this?

/*FIXING CHARACTER DATE INTO NUMERIC DATE 8*/

data SUBMTWO.Labdata29_C;

set SUBMTWO.Labdata29_B;

FORMAT SPECCOLLECTDATE YYMMDD10.;

SPECCOLLECTDATE = INPUT (SPEC_COLLECT_DATE, YYMMDD10.);

DROP SPEC_COLLECT_DATE;

RENAME SPECCOLLECTDATE=SPEC_COLLECT_DATE;

FORMAT DATETEST_LAB YYMMDD10.;

DATETEST_LAB = INPUT (DATETESTLAB, YYMMDD10.);

DROP DATETESTLAB;

RENAME DATETEST_LAB=DATETESTLAB;

FORMAT DATE_DONE1 YYMMDD10.;

DATE_DONE1 = INPUT (DATEDONE1, YYMMDD10.);

DROP DATEDONE1;

RENAME DATE_DONE1=DATEDONE1;

FORMAT ETDATE_DONE YYMMDD10.;

ETDATE_DONE = INPUT (ETDATEDONE, YYMMDD10.);

DROP ETDATEDONE ;

RENAME ETDATE_DONE=ETDATEDONE;

FORMAT DATESENT_EC YYMMDD10.;

DATESENT_EC = INPUT (DATESENTEC, YYMMDD10.);

DROP DATESENTEC;

RENAME DATESENT_EC=DATESENTEC;

FORMAT DATESENT_GT YYMMDD10.;

DATESENT_GT = INPUT (DATESENTGT, YYMMDD10.);

DROP DATESENTGT;

RENAME DATESENT_GT=DATESENTGT;

FORMAT DATESENT_CLIN YYMMDD10.;

DATESENT_CLIN = INPUT (DATESENTCLIN, YYMMDD10.);

DROP DATESENTCLIN;

RENAME DATESENT_CLIN=DATESENTCLIN;

FORMAT DATEALTCDC_PGM YYMMDD10.;

DATEALTCDC_PGM = INPUT (DATEALTCDCPGM, YYMMDD10.);

DROP DATEALTCDCPGM;

RENAME DATEALTCDC_PGM=DATEALTCDCPGM;

FORMAT DATEALTCDC_LAB YYMMDD10.;

DATEALTCDC_LAB = INPUT (DATEALTCDCLAB, YYMMDD10.);

DROP DATEALTCDCLAB;

RENAME DATEALTCDC_LAB=DATEALTCDCLAB;

FORMAT DATESENT_ARLN YYMMDD10.;

DATESENT_ARLN = INPUT (DATESENTARLN, YYMMDD10.);

DROP DATESENTARLN;

RENAME DATESENT_ARLN=DATESENTARLN;

FORMAT DATESENT_CDC YYMMDD10.;

DATESENT_CDC = INPUT (DATESENTCDC, YYMMDD10.);

DROP DATESENTCDC;

RENAME DATESENT_CDC=DATESENTCDC;

RUN;

Re: converting multiple character date into numeric

PaigeMiller — Wed, 06 Feb 2019 16:59:44 GMT

You can use one single format statement, listing all of the variables.

You can use one single DROP statement, listing all of the variables.

Re: converting multiple character date into numeric

tomrvincent — Wed, 06 Feb 2019 17:00:18 GMT

You could do it with a macro loop for each field that meets your criteria, though I don't see a pattern to the names of your output fields.

Re: converting multiple character date into numeric

data_null__ — Wed, 06 Feb 2019 17:42:27 GMT

@Dhana18 wrote:

Hello,

SAS community support is a great resource for me. Than you all.

I need to convert multiple date variables into numeric and this is what I did. Is this right and is there a better efficient way to do this?

/*FIXING CHARACTER DATE INTO NUMERIC DATE 8*/

data SUBMTWO.Labdata29_C;

set SUBMTWO.Labdata29_B;

FORMAT SPECCOLLECTDATE YYMMDD10.;

SPECCOLLECTDATE = INPUT (SPEC_COLLECT_DATE, YYMMDD10.);

DROP SPEC_COLLECT_DATE;

RENAME SPECCOLLECTDATE=SPEC_COLLECT_DATE;

FORMAT DATETEST_LAB YYMMDD10.;

DATETEST_LAB = INPUT (DATETESTLAB, YYMMDD10.);

DROP DATETESTLAB;

RENAME DATETEST_LAB=DATETESTLAB;

FORMAT DATE_DONE1 YYMMDD10.;

DATE_DONE1 = INPUT (DATEDONE1, YYMMDD10.);

DROP DATEDONE1;

RENAME DATE_DONE1=DATEDONE1;

FORMAT ETDATE_DONE YYMMDD10.;

ETDATE_DONE = INPUT (ETDATEDONE, YYMMDD10.);

DROP ETDATEDONE ;

RENAME ETDATE_DONE=ETDATEDONE;

FORMAT DATESENT_EC YYMMDD10.;

DATESENT_EC = INPUT (DATESENTEC, YYMMDD10.);

DROP DATESENTEC;

RENAME DATESENT_EC=DATESENTEC;

FORMAT DATESENT_GT YYMMDD10.;

DATESENT_GT = INPUT (DATESENTGT, YYMMDD10.);

DROP DATESENTGT;

RENAME DATESENT_GT=DATESENTGT;

FORMAT DATESENT_CLIN YYMMDD10.;

DATESENT_CLIN = INPUT (DATESENTCLIN, YYMMDD10.);

DROP DATESENTCLIN;

RENAME DATESENT_CLIN=DATESENTCLIN;

FORMAT DATEALTCDC_PGM YYMMDD10.;

DATEALTCDC_PGM = INPUT (DATEALTCDCPGM, YYMMDD10.);

DROP DATEALTCDCPGM;

RENAME DATEALTCDC_PGM=DATEALTCDCPGM;

FORMAT DATEALTCDC_LAB YYMMDD10.;

DATEALTCDC_LAB = INPUT (DATEALTCDCLAB, YYMMDD10.);

DROP DATEALTCDCLAB;

RENAME DATEALTCDC_LAB=DATEALTCDCLAB;

FORMAT DATESENT_ARLN YYMMDD10.;

DATESENT_ARLN = INPUT (DATESENTARLN, YYMMDD10.);

DROP DATESENTARLN;

RENAME DATESENT_ARLN=DATESENTARLN;

FORMAT DATESENT_CDC YYMMDD10.;

DATESENT_CDC = INPUT (DATESENTCDC, YYMMDD10.);

DROP DATESENTCDC;

RENAME DATESENT_CDC=DATESENTCDC;

RUN;

The code you show is the most efficient way in that you have to read the existing character variable into a new numeric variable and if you want to use(keep) the original name you have to drop and rename as you have done, You can make it a bit easier to code with a macro.

124  %let datevars=SPEC_COLLECT_DATE DATETESTLAB DATEDONE1 ETDATEDONE DATESENTEC DATESENTGT DATESENTCLIN DATEALTCDCPGM
124! DATEALTCDCLAB DATESENTARLN DATESENTCDC;
125
126  %macro chr2num(vars=,informat=);
127     %local i w;
128     %let i = %eval(&i+1);
129     %let w = %scan(%superq(vars),&i);
130     %do %while(%superq(w) ne);
131        _x&i = input(&w,&informat);
132        rename _x&i = &w;
133        drop &w;
134        %let i = %eval(&i+1);
135        %let w = %scan(%superq(vars),&i);
136        %end;
137     %mend chr2num;
138
139  options mprint=1;
140  data _null_;
141     %chr2num(vars=&datevars,informat=yymmdd10.);
MPRINT(CHR2NUM):   _x1 = input(SPEC_COLLECT_DATE,yymmdd10.);
MPRINT(CHR2NUM):   rename _x1 = SPEC_COLLECT_DATE;
MPRINT(CHR2NUM):   drop SPEC_COLLECT_DATE;
MPRINT(CHR2NUM):   _x2 = input(DATETESTLAB,yymmdd10.);
MPRINT(CHR2NUM):   rename _x2 = DATETESTLAB;
MPRINT(CHR2NUM):   drop DATETESTLAB;
MPRINT(CHR2NUM):   _x3 = input(DATEDONE1,yymmdd10.);
MPRINT(CHR2NUM):   rename _x3 = DATEDONE1;
MPRINT(CHR2NUM):   drop DATEDONE1;
MPRINT(CHR2NUM):   _x4 = input(ETDATEDONE,yymmdd10.);
MPRINT(CHR2NUM):   rename _x4 = ETDATEDONE;
MPRINT(CHR2NUM):   drop ETDATEDONE;
MPRINT(CHR2NUM):   _x5 = input(DATESENTEC,yymmdd10.);
MPRINT(CHR2NUM):   rename _x5 = DATESENTEC;
MPRINT(CHR2NUM):   drop DATESENTEC;
MPRINT(CHR2NUM):   _x6 = input(DATESENTGT,yymmdd10.);
MPRINT(CHR2NUM):   rename _x6 = DATESENTGT;
MPRINT(CHR2NUM):   drop DATESENTGT;
MPRINT(CHR2NUM):   _x7 = input(DATESENTCLIN,yymmdd10.);
MPRINT(CHR2NUM):   rename _x7 = DATESENTCLIN;
MPRINT(CHR2NUM):   drop DATESENTCLIN;
MPRINT(CHR2NUM):   _x8 = input(DATEALTCDCPGM,yymmdd10.);
MPRINT(CHR2NUM):   rename _x8 = DATEALTCDCPGM;
MPRINT(CHR2NUM):   drop DATEALTCDCPGM;
MPRINT(CHR2NUM):   _x9 = input(DATEALTCDCLAB,yymmdd10.);
MPRINT(CHR2NUM):   rename _x9 = DATEALTCDCLAB;
MPRINT(CHR2NUM):   drop DATEALTCDCLAB;
MPRINT(CHR2NUM):   _x10 = input(DATESENTARLN,yymmdd10.);
MPRINT(CHR2NUM):   rename _x10 = DATESENTARLN;
MPRINT(CHR2NUM):   drop DATESENTARLN;
MPRINT(CHR2NUM):   _x11 = input(DATESENTCDC,yymmdd10.);
MPRINT(CHR2NUM):   rename _x11 = DATESENTCDC;
MPRINT(CHR2NUM):   drop DATESENTCDC;
142     run;

Re: converting multiple character date into numeric

ballardw — Wed, 06 Feb 2019 18:30:05 GMT

If this is going to be an ongoing issue with repeated data sets from a source then I would suggest that you want to consider how you are bringing the data into SAS. Often this sort of conversion is needed due to use of Proc Import which guesses, and sometimes fails, as to variable types.

Use of a data step to read an appropriate file format such as CSV, tab or other character delimited or fixed columns means that the data could be read directly into SAS date values and no conversion needed.