topic Re: correct DATETIME20. with wrong century? in SAS Programming

correct DATETIME20. with wrong century?

kjohnsonm — Fri, 04 Nov 2016 21:44:37 GMT

Hello all,

I just discovered that I have a relatively large data set that was incorrectly converted from yymmdd to

DATETIME20.

Dates still kick my hinny after a year of working with sas but in any case any date that matches this needs to have the century changed from 19 to 20

data data.COMBOx;
set combo2;

if (year(orig_hire) < year(birth_date)) and (year > 1999) then orig_hire=

/* year(orig_hire)+100 month(orig_hire) date(orig_hire) etc*/

run;

I know how to parse it, I get mixed up when puting it bad together. help please. TIA.

Re: correct DATETIME20. with wrong century?

Reeza — Fri, 04 Nov 2016 21:56:17 GMT

Can you post some sample data please.

In general you could just use INTNX and increment it 100 years.

Re: correct DATETIME20. with wrong century?

kjohnsonm — Fri, 04 Nov 2016 22:02:58 GMT

Oh I had not see that function before, cool. Let me try it before going down the sample data path. Thank you.

Re: correct DATETIME20. with wrong century?

kjohnsonm — Fri, 04 Nov 2016 22:27:42 GMT

proc print data=combo2 (obs=10) noobs n;

var orig_hire birth_date year;

/*where year(orig_hire) < year(birth_date) and year > '1999';*/

where orig_hire < birth_date and year > '1999';

run;

orig_hire birth_date year

05SEP1900:00:00:00 05JAN1957:00:00:00 2000

10JUL1900:00:00:00 05MAY1970:00:00:00 2000

16AUG1900:00:00:00 28JUN1969:00:00:00 2000

01JAN1900:00:00:00 01APR1960:00:00:00 2000

16AUG1900:00:00:00 11FEB1957:00:00:00 2000

01MAY1900:00:00:00 19AUG1967:00:00:00 2000

01SEP1900:00:00:00 27AUG1954:00:00:00 2000

01MAR1900:00:00:00 25FEB1951:00:00:00 2000

16AUG1900:00:00:00 04MAR1971:00:00:00 2000

N = 10

my code:

data data.COMBOx;
set combo2;

/* example x=intnx('week', '17oct03'd, 6);*/
if (year(orig_hire) < year(birth_date)) and (year > 1999) then orig_hire=intnx('year', orig_hire, 100, 'S');
run;

Should this work? It is bombing out. The date is already stored as DATETIME20. TIA.

Re: correct DATETIME20. with wrong century?

Reeza — Fri, 04 Nov 2016 22:40:36 GMT

Rather than year, try DTYEAR to specify you're working with a date time.

Re: correct DATETIME20. with wrong century?

kjohnsonm — Fri, 04 Nov 2016 22:52:00 GMT

I am getting hudreds of these notes then it dies. "this is the internal date"? I am giving it the field name 'orig_hire'. I tried adding datetime20. no go...:

NOTE: Invalid argument to function INTNX('year',-1873756800,100,'S') at line 1599 column 60.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

AGE_0815=32 WSU_ID_Mfix= PSN_NO_Mfix= AREA_NAME=PROVOST, OFFICE OF EEO=2 SFILE=ALL_00.DBF STRM=2007 TITLE=INSTRUCTOR WORK_LOCN=PUL WSUID=09993982 TITLE_SHRT= year=2000 WSU_ID=9993982 EMPSTATRSN=00 PRIMEMPTYP=F FULL_PART=P PERM_TEMP=T SEX=M OFF_CAMPUS=

HOME_DEPT=8324 WORK_COUTY=0038 RET_PLAN= SEIB_ELIG= ETHNIC=50 TENURE_IND=N CITIZENSHP=US MILITARY=K HANDICAP= TENURE_STA=N TENURE_YR=0000 HIGHDEG_CD=08 HIGHDEG_YR=93 PSN_NO=077281 ACTIVE_IND=Y PAY_AFFT=Y LEAVE_IND=N PAYRATE_CD=M HEPB_STAT=7 TIMERPT_CD=E

TERM=9.0000 PAYRATE=833.330 FTE=25.00000 FULLTM_RTE=3333.33 JOB_CLASS=000260 STEP= PID_MO=00 APPT_DEPT=8324 LEAVE_RSN= SAL_VAR= RANGE= SAL_REV_AR= CLASS_TYPE=F APPT_WK_PD= NAME=SMITH,WILLIAM LELAND EMPL_STAT=A JOBGROUP=21 ANNUAL_9B=29999.97

ANNUAL_12B=. ANNUAL=29999.97 AREA=01 OFMGROUP=03 DEPT_NAME=GENERAL EDUCATION HOME_AREA=01 OFM_GROUP= OFM_CLASS= TOTALIFFTE=. AREANAMEN= orig_hireC6= home_deptnum=. job_classnum=. apptdeptname_off= areaname= home_deptname_off= PSN_NO_ORIG=.

SAL_RANGE= OFM= campus=Pullman WSUID_F= WSUID_Mfix= record_nbr=1 HOME_AREA_DESCR=PROVOST, OFFICE OF HOME_DEPT_DESCR=GENERAL EDUCATION annual_new=29999.97 annual_12b_new=39999.96 annual_9b_new=29999.97 emp_pri_ind=0 emp_nex_ind=1 C_emp_pri_ind=0

C_emp_nex_ind=0 E_emp_pri_ind=0 E_emp_nex_ind=0 F_emp_pri_ind=0 F_emp_nex_ind=1 G_emp_pri_ind=0 G_emp_nex_ind=0 end_date=15MAY1901:00:00:00 enddate=15MAY2001:00:00:00 end_datec6=. birth_date=27SEP1967:00:00:00 statdate=16AUG1994:00:00:00 retn_date=.

prob_date=. cont_empl=16AUG1900:00:00:00 orig_hire=. prob_datec6=. retn_datec6=. retdate_c8=. statdatec6=. snapshot_date=. begin_date=16AUG2000:00:00:00 cont_emplC6=. totalfte=25 sum_allfte=25 _ERROR_=1 _N_=58713

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

22 at 1599:60

NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.

Each place is given by: (Number of times) at (Line):(Column).

126773 at 1599:60

NOTE: There were 298416 observations read from the data set WORK.COMBO2.

NOTE: The data set DATA.COMBOX has 298416 observations and 107 variables.

NOTE: DATA statement used (Total process time):

real time 28.65 seconds

cpu time 0.79 seconds

Re: correct DATETIME20. with wrong century?

kjohnsonm — Fri, 04 Nov 2016 23:02:24 GMT

okay the "if line" fix is:
if orig_hire < birth_date and year > '1999' and orig_hire <> . then orig_hire=intnx('dtyear', orig_hire, 100, 'S');

Re: correct DATETIME20. with wrong century?

Reeza — Fri, 04 Nov 2016 23:45:10 GMT

Is the year portion of that correct? Why are you storing it as a character...that could cause you issues.

Re: correct DATETIME20. with wrong century?

kjohnsonm — Sat, 05 Nov 2016 00:01:03 GMT

long story

Just a hard coded year for information only per record. I work at a School and it's easer to read than a term number. We can dump it when we are done since each record has the term indicator. We are stacking all the records from 50+ files once the data types are all in line and 5-10 differnt staff mebers doing this processing over the years. Thank you for your help and attention to my coding. -KJ