working with date

[Kyra]

Hi,

i have a dataset where operation date is stored as MM/DD/YYYY format.

I want to categoize the dataset into 3 groups according to the following codes. Please let me know where am i making the mistake.

Thanks.

data red.colonbundle3;
set red.colonbundle2;
input Operation_Date:mmddyy10. ;
if Operation_Date<='10/31/2014'd then date=1;
else if '11/1/2014'd<=Operation_Date<='10/31/2107'd then year=2;
else year=3;
run;

[novinosrin]

else if '11/1/2014'd<=Operation_Date<='10/31/2107'd then year=2;

For a minute I forgot what year we are in, not in 2107

also sas data constants is in the form '01NOV2014'd

Doc Reference:

Date, Time, and Datetime Constants

You can create a date constant, time constant, or datetime constant by specifying the date or time in single or double quotation marks, followed by a D (date), T (time), or DT (datetime) to indicate the type of value.

Any trailing blanks or leading blanks included within the quotation marks will not affect the processing of the date constant, time constant, or datetime constant.

Use the following patterns to create date and time constants:

'ddmmm<yy>yy'D or "ddmmm<yy>yy"D represents a SAS date value:

• date='1jan2006'd;

• date='01jan04'd;

[Kyra]

Please disregard the previous text.

Below is the code i am using

data red.colonbundle3;
set red.colonbundle2;
input Operation_Date:mmddyy10.;
if Operation_Date<='10/31/2014'd then date=1;
else if '11/1/2014'd<=Operation_Date<='10/31/2107'd then date=2;
else date=3;
run;

[Kurt_Bremser]

A SAS date literal (in other programming languages you would say "constant") hast to come in the format "DDMMMYYYY"d, where MMM are the first three letters of the month name. So you should use

'31oct2014'd
'01nov2014'd

and so on.

[Kyra]

Upon running the below codes the value for date is 1 for all of them.  There is still something off with the code. please help. Once again, date in the dataset looks like MM/DD/YYYY.

data red.colonbundle3;
set red.colonbundle2;
if Operation_Date<='31oct2014'd then date=1;
else if '01nov2014'd<=Operation_Date<='31oct2017'd then date=2;
else date=3;
run;

[Kurt_Bremser]

Is operation_date a SAS date? Meaning, type is numeric, and format is MMDDYY10.

For clarification, post the log of your data step.

[Kyra]

Pasting below part of the log-

25 data red.colonbundle3;
26 set red.colonbundle2;
27 if Operation_Date <= 10/31/2104 then date=1;
28 else if 11/1/2014 <= Operation_Date <= 10/31/2107 then date=2;
29 else date=3;
30 run;

NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
27:4 28:22
NOTE: Invalid numeric data, Operation_Date='01/04/2013' , at line 27 column 4.
LMRN=7221262 Date_of_Birth=01/10/1989 Age_at_Time_of_Surgery=23.98 Gender=Female Race=White
Hispanic_Ethnicity=No CPT_Code=44207
CPT_Description=Laparoscopy, surgical; colectomy, partial, with anastomosis, with coloproctostom
y (low pelvic anastomosis) Principal_Operative_Procedure= In_Out_Patient_Status=Inpatient
Elective_Surgery=Yes Origin_Status=Not transferred (admitted from home)
Hospital_Admission_Date=01/04/2013 Operation_Date=01/04/2013
Principal_Anesthesia_Technique=General Additional_Anesthesia_Technique_=

on runing the proc contents the below shows up about operation date :

SAS Output

Operation_Date

Char

10

$10.

$10.

Operation Date

[PaigeMiller]

Operation_date is character, and so the code you have won't work, it expects numbers. You have to create a numeric SAS date variable from your character variable and then things will work. If you can show us a few of the values of Operation_Date, we could probably explain how to do that.

UPDATE

I see this now in your SASLOG

NOTE: Invalid numeric data, Operation_Date='01/04/2013' , at line 27 column 4.

so to convert this to an actual numeric date, try

num_date=input(operation_date,mmddyy10.);

followed by

if num_Date <= '31OCT2014'd then date=1;
else if '01NOV2014'd <= num_Date <= '31OCT17'd then date=2;

etc.

Of course, another (and probably better way) to handle this is to use formats rather than date=1 or date=2.

[Kyra]

some of the values of operation_date are-

01/04/2013

02/04/2013

02/05/2013

01/31/2013

02/04/2013

thanks,

[Kurt_Bremser]

Which means your operation_date is not a SAS date, but a character variable.

Make sure that dates are imported as such when data arrives in your data warehouse.

[Kyra]

sorry wrong log was pasted in previous text

195 data red.colonbundle3;
196 set red.colonbundle2;
197
198 if Operation_Date<='31oct2014'd then date=1;
199 else if '01nov2014'd<=Operation_Date<='31oct2017'd then date=2;
200 else date=3;
201 run;

NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
198:4 199:23
NOTE: Invalid numeric data, Operation_Date='01/04/2013' , at line 198 column 4.
LMRN=7221262 Date_of_Birth=01/10/1989 Age_at_Time_of_Surgery=23.98 Gender=Female Race=White
Hispanic_Ethnicity=No CPT_Code=44207
CPT_Description=Laparoscopy, surgical; colectomy, partial, with anastomosis, with coloproctostom
y (low pelvic anastomosis) Principal_Operative_Procedure= In_Out_Patient_Status=Inpatient
Elective_Surgery=Yes Origin_Status=Not transferred (admitted from home)
Hospital_Admission_Date=01/04/2013 Operation_Date=01/04/2013
Principal_Anesthesia_Technique=General Additional_Anesthesia_Technique_=
Attending_Staff_Surgeon=KHAITOV,SERGEY (KHAITOV, SERGEY) Height=170.2 Height_Unit=cm
Weight=45.45 Weight_Unit=kg BMI=15.69 Diabetes_Mellitus=No Current_Smoker_within_1_year=No
Dyspnea=No Functional_Heath_Status=Independent Ventilator_Dependent=No History_of_Severe_COPD=No
Ascites_w_in_30_days_prior_to_su=No Congestive_Heart_Failure_w_in_30=No
Hypertension_requiring_medicatio=No Acute_Renal_Failure=No Currently_requiring_or_on_dialys=No
Disseminated_Cancer=No Open_Wound_with_or_without_infec=No Steroid_Immunosuppressant_use_fo=No
_10__loss_of_body_weight_in_the_=No Bleeding_Disorder=No Preop_Transfusions__RBC_w_in_72_=No
Sepsis__SIRS_Sepsis_Septic_shock=None All_Pre_op_Labs_Unknown= Serum_Sodium=136
Serum_Sodium_Date=12/14/2012 BUN=14 BUN_Date=12/14/2012 Serum_Creatinine=0.74
Serum_Creatinine_Date=12/14/2012 Albumin=4.5 Albumin_Date=12/14/2012 Total_Bilirubin=0.4
Total_Bilirubin_Date=12/14/2012 AST_SGOT=15 AST_SGOT_Date=12/14/2012 Alkaline_Phosphatase=63
Alkaline_Phosphatase_Date=12/14/2012 WBC=7.7 WBC_Date=12/14/2012 Hematocrit=39.7
Hematocrit_Date=12/14/2012 Platelet_Count=240 Platelet_Count_Date=12/14/2012 INR=1
INR_Date=12/14/2012 PTT=25.7 PTT_Date=12/14/2012 Emergency_Case=No
Wound_Classification=Clean/Contaminated
Surgical_Wound_s__Closure=All layers of incision (deep and superficial) fully closed
ASA_Classification=ASA 2 - Mild Disturb Duration_of_Surgical_Procedure__=83
__of_Other_Procedures=0 Other_Procedures_CPT= __of_Concurrent_Procedures=0
Concurrent_Procedures_CPT= __of_Postop_Superficial_Incision=0
__of_Postop_Superficial_Incisio0=0 __of_Postop_Deep_Incisional_SSI=0
__of_Postop_Deep_Incisional_SSI_=0 __of_Postop_Organ_Space_SSI=0
__of_Postop_Organ_Space_SSI_PATO=0 __of_Postop_Wound_Disruption=0 __of_Postop_Pneumonia=0
__of_Postop_Pneumonia_PATOS=0 __of_Postop_Unplanned_Intubation=0
__of_Postop_Pulmonary_Embolism=0 __of_Postop_On_Ventilator___48_h=0
__of_Postop_On_Ventilator___48_0=0 __of_Postop_Progressive_Renal_In=0
__of_Postop_Acute_Renal_Failure=0 __of_Postop_UTI=0 __of_Postop_UTI_PATOS=0 __of_Postop_CVA=0
__of_Postop_Cardiac_Arrest_Requi=0 __of_Postop_Myocardial_Infarctio=0
__of_Postop_Transfusion_Intraop_=0 Total_Blood_Transfused__in_units=
__of_Postop_Vein_Thrombosis_Requ=0 __of_Postop_C__diff=0 Type_of_C__diff_test_performed=
Result_of_C__diff_test= Was_patient_given_treatment_for_= Did_patient_with_C__diff_have_di=
__of_Postop_Sepsis=0 __of_Postop_Sepsis_PATOS=0 __of_Postop_Septic_Shock=0
__of_Postop_Septic_Shock_PATOS=0 __of_Postop_Other_Occurrences=0
Acute_Hospital_Discharge_Date=01/08/2013 Hospital_Length_of_Stay=4
Hospital_Discharge_Destination=Home Postoperative_ICD9_Code=555.9
Postoperative_ICD9_Description=REGIONAL ENTERITIS OF UNSPECIFIED SITE Postoperative_ICD10_Code=
Postoperative_ICD10_Description= Still_in_Hospital__30_Days=
Postop_Death_w_in_30_days_of_Pro=No Postoperative_Death___30_days_of=No Date_of_Death=
Date_of_Death_Unknown= End_of_Life_Withdrawal_of_Care= __of_Readmissions_w_in_30_days=0
Date_of_First_Readmission= __of_Unplanned_Readmissions=0 __of_Readmissions_likely_related=0
__of_Readmissions_likely_unrelat=0 First_Unplanned_return_to_OR=No
Date__of_First_Unplanned_Return_= __of_First_Unplanned_Return_Proc=0
First_Unplanned_Return_CPT= First_Unplanned_Return_Related_t= Second_unplanned_return_to_OR=
Date_of_Second_unplanned_return= __of_Second_Unplanned_Return_Pro=0
Second_Unplanned_Return_CPT= Second_Unplanned_Return_Related_=
More_than_2_unplanned_returns_to= Colectomy_Steroid_Immunosuppress=No
Colectomy_Steroid_Immunosuppres0= Colectomy_Mechanical_Bowel_Prep=No
Colectomy_Mechanical_Bowel_Prep_= Colectomy_Oral_Antibiotic_Prep=Yes
Colectomy_Oral_Antibiotic_Prep_U= Colectomy_Primary_Indication_for=Crohn's Disease
Colectomy_Non_Emergent_Indicatio= Colectomy_Non_Emergent_Indicati0=
Colectomy_Indication_for_Surgery= Colectomy_Emergent_Indication_IC=
Colectomy_Emergent_Indication_I0= Colectomy_Operative_Approach=Laparoscopic
Colectomy_Postop_Anastomotic_Lea=No definitive diagnosis of leak/leak related abscess
Colectomy_Prolonged_Postoperativ=No Colectomy_Prolonged_Postoperati0=
Proctectomy_Clear_Distal_Margin_= Proctectomy_Operative_Approach=
Proctectomy_Postop_Anastomotic_L= Proctectomy_Prolonged_Postoperat=
Proctectomy_Prolonged_Postopera0= _0_Day_F_U_Complete=Yes Follow_up_Days= date=1 _ERROR_=1
_N_=1
NOTE: Invalid numeric data, Operation_Date='01/04/2013' , at line 198 column 4.
LMRN=2384934 Date_of_Birth=03/29/1956 Age_at_Time_of_Surgery=56.77 Gender=Female Race=White
Hispanic_Ethnicity=No CPT_Code=45110
CPT_Description=Proctectomy; complete, combined abdominoperineal, with colostomy
Principal_Operative_Procedure=Bilateral ureteral stent placement and cystoscopy by Urology.
2. E
xploratory laparotomy.
3. Lysis of adhesions.
4. Abdominoperineal resection of J-pouch.
5. End i
leostomy. In_Out_Patient_Status=Inpatient Elective_Surgery=No
Origin_Status=Not transferred (admitted from home) Hospital_Admission_Date=01/03/2013
Operation_Date=01/04/2013 Principal_Anesthesia_Technique=General
Additional_Anesthesia_Technique_= Attending_Staff_Surgeon=BAUER,J. (BAUER, J.) Height=69
Height_Unit=in Weight=68 Weight_Unit=kg BMI=22.14 Diabetes_Mellitus=No
Current_Smoker_within_1_year=No Dyspnea=No Functional_Heath_Status=Independent
Ventilator_Dependent=No History_of_Severe_COPD=No Ascites_w_in_30_days_prior_to_su=No
Congestive_Heart_Failure_w_in_30=No Hypertension_requiring_medicatio=No Acute_Renal_Failure=No
Currently_requiring_or_on_dialys=No Disseminated_Cancer=No Open_Wound_with_or_without_infec=No
Steroid_Immunosuppressant_use_fo=Yes _10__loss_of_body_weight_in_the_=No Bleeding_Disorder=No
Preop_Transfusions__RBC_w_in_72_=No Sepsis__SIRS_Sepsis_Septic_shock=None
All_Pre_op_Labs_Unknown= Serum_Sodium=137 Serum_Sodium_Date=01/03/2013 BUN=12
BUN_Date=01/03/2013 Serum_Creatinine=0.76 Serum_Creatinine_Date=01/03/2013 Albumin=4
Albumin_Date=01/03/2013 Total_Bilirubin=0.4 Total_Bilirubin_Date=01/03/2013 AST_SGOT=19
AST_SGOT_Date=01/03/2013 Alkaline_Phosphatase=71 Alkaline_Phosphatase_Date=01/03/2013 WBC=6.1
WBC_Date=01/03/2013 Hematocrit=35.1 Hematocrit_Date=01/03/2013 Platelet_Count=172
Platelet_Count_Date=01/03/2013 INR=1 INR_Date=01/03/2013 PTT=40.9 PTT_Date=01/03/2013
Emergency_Case=No Wound_Classification=Clean/Contaminated
Surgical_Wound_s__Closure=All layers of incision (deep and superficial) fully closed
ASA_Classification=ASA 3 - Severe Disturb Duration_of_Surgical_Procedure__=262
__of_Other_Procedures=0 Other_Procedures_CPT= __of_Concurrent_Procedures=0
Concurrent_Procedures_CPT= __of_Postop_Superficial_Incision=0
__of_Postop_Superficial_Incisio0=0 __of_Postop_Deep_Incisional_SSI=0
__of_Postop_Deep_Incisional_SSI_=0 __of_Postop_Organ_Space_SSI=0
__of_Postop_Organ_Space_SSI_PATO=0 __of_Postop_Wound_Disruption=0 __of_Postop_Pneumonia=0
__of_Postop_Pneumonia_PATOS=0 __of_Postop_Unplanned_Intubation=0
__of_Postop_Pulmonary_Embolism=0 __of_Postop_On_Ventilator___48_h=0
__of_Postop_On_Ventilator___48_0=0 __of_Postop_Progressive_Renal_In=0
__of_Postop_Acute_Renal_Failure=0 __of_Postop_UTI=0 __of_Postop_UTI_PATOS=0 __of_Postop_CVA=0
__of_Postop_Cardiac_Arrest_Requi=0 __of_Postop_Myocardial_Infarctio=0
__of_Postop_Transfusion_Intraop_=0 Total_Blood_Transfused__in_units=
__of_Postop_Vein_Thrombosis_Requ=0 __of_Postop_C__diff=0 Type_of_C__diff_test_performed=
Result_of_C__diff_test= Was_patient_given_treatment_for_= Did_patient_with_C__diff_have_di=
__of_Postop_Sepsis=0 __of_Postop_Sepsis_PATOS=0 __of_Postop_Septic_Shock=0
__of_Postop_Septic_Shock_PATOS=0 __of_Postop_Other_Occurrences=0
Acute_Hospital_Discharge_Date=01/09/2013 Hospital_Length_of_Stay=6
Hospital_Discharge_Destination=Home Postoperative_ICD9_Code=555.9
Postoperative_ICD9_Description=REGIONAL ENTERITIS OF UNSPECIFIED SITE Postoperative_ICD10_Code=
Postoperative_ICD10_Description= Still_in_Hospital__30_Days=
Postop_Death_w_in_30_days_of_Pro=No Postoperative_Death___30_days_of=No Date_of_Death=
Date_of_Death_Unknown= End_of_Life_Withdrawal_of_Care= __of_Readmissions_w_in_30_days=0
Date_of_First_Readmission= __of_Unplanned_Readmissions=0 __of_Readmissions_likely_related=0
__of_Readmissions_likely_unrelat=0 First_Unplanned_return_to_OR=No
Date__of_First_Unplanned_Return_= __of_First_Unplanned_Return_Proc=0
First_Unplanned_Return_CPT= First_Unplanned_Return_Related_t= Second_unplanned_return_to_OR=
Date_of_Second_unplanned_return= __of_Second_Unplanned_Return_Pro=0
Second_Unplanned_Return_CPT= Second_Unplanned_Return_Related_=
More_than_2_unplanned_returns_to= Colectomy_Steroid_Immunosuppress=
Colectomy_Steroid_Immunosuppres0= Colectomy_Mechanical_Bowel_Prep=
Colectomy_Mechanical_Bowel_Prep_= Colectomy_Oral_Antibiotic_Prep=
Colectomy_Oral_Antibiotic_Prep_U= Colectomy_Primary_Indication_for=
Colectomy_Non_Emergent_Indicatio= Colectomy_Non_Emergent_Indicati0=
Colectomy_Indication_for_Surgery= Colectomy_Emergent_Indication_IC=
Colectomy_Emergent_Indication_I0= Colectomy_Operative_Approach=
Colectomy_Postop_Anastomotic_Lea= Colectomy_Prolonged_Postoperativ=
Colectomy_Prolonged_Postoperati0= Proctectomy_Clear_Distal_Margin_=
Proctectomy_Operative_Approach=Open (planned)
Proctectomy_Postop_Anastomotic_L=No definitive diagnosis of leak/leak related abscess
Proctectomy_Prolonged_Postoperat=No Proctectomy_Prolonged_Postopera0= _0_Day_F_U_Complete=No
Follow_up_Days=20 date=1 _ERROR_=1 _N_=2
NOTE: Invalid numeric data, Operation_Date='01/04/2013' , at line 198 column 4.
LMRN=3649465 Date_of_Birth=06/13/1954 Age_at_Time_of_Surgery=58.56 Gender=Male Race=White
Hispanic_Ethnicity=No CPT_Code=44310 CPT_Description=Ileostomy or jejunostomy, non-tube
Principal_Operative_Procedure= In_Out_Patient_Status=Inpatient Elective_Surgery=Yes
Origin_Status=Not transferred (admitted from home) Hospital_Admission_Date=01/04/2013
Operation_Date=01/04/2013 Principal_Anesthesia_Technique=General
Additional_Anesthesia_Technique_=General Attending_Staff_Surgeon=BAUER,J. (BAUER, J.) Height=70
Height_Unit=in Weight=80 Weight_Unit=kg BMI=25.31 Diabetes_Mellitus=Non-Insulin
Current_Smoker_within_1_year=No Dyspnea=No Functional_Heath_Status=Independent
Ventilator_Dependen

On running proc contents the following shows up about operation date.

SAS Output

Operation_Date

Char

10

$10.

$10.

Operation Date

[PaigeMiller · Accepted solution]

I believe my earlier reply (Message 8 in this thread) still applies here.

[Kyra]

Yes , the code you gave me in messase 8 worked.

Thanks a lot. This community has been a great help to me.