DATA Step, Macro, Functions and more

correct DATETIME20. with wrong century?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

correct DATETIME20. with wrong century?

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.


Accepted Solutions
Solution
‎11-04-2016 07:01 PM
Super User
Posts: 19,767

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm

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

View solution in original post


All Replies
Super User
Posts: 19,767

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm

Can you post some sample data please. 

 

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

Frequent Contributor
Posts: 90

Re: correct DATETIME20. with wrong century?

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

Frequent Contributor
Posts: 90

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm

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

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.

 

Solution
‎11-04-2016 07:01 PM
Super User
Posts: 19,767

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm

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

Frequent Contributor
Posts: 90

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm

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)Smiley SadColumn).

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)Smiley SadColumn).

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

 

Frequent Contributor
Posts: 90

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm
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');
Super User
Posts: 19,767

Re: correct DATETIME20. with wrong century?

Posted in reply to kjohnsonm

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

Frequent Contributor
Posts: 90

Re: correct DATETIME20. with wrong century?

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 341 views
  • 2 likes
  • 2 in conversation