Hi Jim,   Unfortunately the DOB is still in the char format.  Rather than copy a a **bleep** of the output dataset these are the first 10 values for DOB:   DOB 38407 29738 42385 37471 34608 26080 39334 37308 38671 27413 ... View more

The DOB will be in the date-time format like the one you mentioned (below). However, not sure why the year = 1861. Other date-time related fields in the file that I have seen are of this format but some aren't. For example, the following field Created_On=01AUG20:08:33:54 has a different datepart. Very little consistency in the file. Not sure why it is set up that way. Data comes from contact tracer records from the field. DOB=04/03/1861 00:00:00 wlierman ... View more

WHen I checked the SAS output file the DOB var was still like 38476 24564 etc ... View more

I ran the second code block Data Arias.Merge_data_E_18; Set Arias.Merge_data_E_18; New_DOB = PUT(INPUT(DOB, 5.), MMDDYYS10.); run; and received the following log Data Arias.Merge_data_E_18; 363 Set Arias.Merge_data_E_18; 364 New_DOB = PUT(INPUT(DOB, 5.), MMDDYYS10.); 365 run; NOTE: Invalid argument to function INPUT at line 364 column 17. County=Jackson Jurisdiction=Jackson CHD Contact_number=dbac2a5f-0cd4-ea11-a813-001dd83096fe _Do_Not_Modify__Row_Checksum=MvbQ4ucFSa06yXIUQuV9uanIv0tnSeKcliEG0lVvJeQwDITgs0d6E2l4rdktOUpsBptzwsZXA R6WWLZisKos1w== _Do_Not_Modify__Modified_On=04SEP20:01:06:04 Person_ID=956838 Contact_ID=5028693 Case_ID=774942 _Full_Name=Josh Charleboix First_Name=Josh Middle_Name= Last_Name=Charleboix Contact_Attempt_Successful=Yes Created_On=01AUG20:08:33:54 Date_of_Last_Exposure=07/23/2020 _1st_Contact_Attempt=01AUG20:15:30:00 _2nd_Contact_Attempt=02AUG20:13:30:00 _3rd_Contact_Attempt=8 Owner=Natasha Ching DOB=04/03/1861 00:00:00 Monitoring_Status=Lost to Follow-up Status=Inactive Address_1=411 berrydale Ave #9 MEDFORD, or 97501 City=MEDFORD Preferred_Method_of_Contact=Text Email=joshcharleboix@gmail.com Mobile_Phone=541-973-5460 End_of_Quarantine_Period=08/06/2020 Language=English New_DOB=. _ERROR_=1 _N_=35443 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). 1 at 364:17 NOTE: There were 41280 observations read from the data set ARIAS.MERGE_DATA_E_18. NOTE: The data set ARIAS.MERGE_DATA_E_18 has 41280 observations and 30 variables. NOTE: Compressing data set ARIAS.MERGE_DATA_E_18 decreased size by 46.38 percent. Compressed is 252 pages; un-compressed would require 470 pages. NOTE: DATA statement used (Total process time): real time 0.17 seconds cpu time 0.12 seconds Which was only 1 time for that error.  Why did that occur? I can probably live with one line. ... View more

Yes. The Proc contents 20 DOB Char 19 $19. $19. DOB ... View more

Hi Jim, I am confounded now!  I coded the data step like this Data Arias.Merge_data_E_18; Set Arias.Merge_data_E_18; New_DOB = PUT(DOB, MMDDYYS10.); run; I thought it was working but then I got the log back 324 Data Arias.Merge_data_E_18; 325 Set Arias.Merge_data_E_18; 326 New_DOB = PUT(DOB, mmddyys10.); ---------- 48 ERROR 48-59: The format $MMDDYYS was not found or could not be loaded. 327 run; NOTE: The SAS System stopped processing this step because of errors. Okay so I looked in the Step-by_Step Base SAS manual read how to assign the MMDDYY10. format as   format DepartureDate mmddyy10.;        (using the var name form the book)   I tried it as Data Arias.Merge_data_E_18; Set Arias.Merge_data_E_18; Format DOB mmddyys10.; *New_DOB = PUT(DOB, MMDDYYS10.); run; The log returned Data Arias.Merge_data_E_18; 356 Set Arias.Merge_data_E_18; 357 Format DOB mmddyys10.; ---------- 48 ERROR 48-59: The format $MMDDYYS was not found or could not be loaded. 358 *New_DOB = PUT(DOB, MMDDYYS10.); 359 run; So I am stumped again. Appreciate your assistance.   wlierman ... View more

Hi Jim,   Thank you for the spot-on solution to this problem.   I appreciate your quick response and you taking the time to answer.   Take care.   wlierman ... View more

Yes.  I read right over the solution by Paige.  The label is the ugly expression.  But the variable name is _Do_Not_Modify Contact  which is still not all that straightforward but at least has modify spelled correctly.   This also ties to your answer too.  Thank you.   wlierman ... View more

Sorry, but I read right over your solution.  Checking the View Columns the Label is the (Do Not Modfy)Contact but the variable name is _Do_Not_Modify Contact  with modify actually spelled correctly.   So thank you for your help.  Still learned something new on the name literal.   wlierman ... View more

I ran the code and while it may look like there is a space between the ) and Contact there isn't any extra space.  So I ran this Data Arias.System_contacts (rename = ( '(Do Not Modfy)Contact'n = Contact_number)); Set ARIAS.System_Contacts_per_tracer; run; and got the following log result:   99 Data Arias.System_contacts (rename = ( '(Do Not Modfy)Contact'n = Contact_number)); ------------------------ 214 23 ERROR 214-322: Variable name '(Do Not Modfy)Contact'n is not valid. ERROR 23-7: Invalid value for the RENAME option. 100 Set ARIAS.System_Contacts_per_tracer; 101 run; I tried one additional space between the closing ' and n but still no successful run.   Any there another approach?   Thanks.   wlierman ... View more

Thank you for the follow-up information.  I will be sure to follow-up, since the data will continue to come from contact tracers in the field then through an epidemiology-based data file.  Could be plenty of places for extraneous characters to be introduced.   Thank you agaoin.   wlierman ... View more

Thank you for the analysis. I ran the proc import again keeping the file as an xlsx. The row count was a little higher than my "good" import. But the log shows a lot of adjustments that SAS made Proc import datafile = "I:\Health Analytics\AMB_INFORMATICS\Arias_All_Contacts_Excel_Upload\Copy 13 ! of All Sys Contacts 9-7-2020 11-41-02 AM.xlsx" 14 out = ARIAS.System_Contacts_per_tracer 15 dbms = xlsx 16 replace; 17 getnames=yes; 18 run; NOTE: Variable Name Change. (Do Not Modify) Contact -> _Do_Not_Modify__Contact NOTE: Variable Name Change. (Do Not Modify) Row Checksum -> _Do_Not_Modify__Row_Checksum NOTE: Variable Name Change. (Do Not Modify) Modified On -> _Do_Not_Modify__Modified_On NOTE: Variable Name Change. Last Name -> Last_Name NOTE: Variable Name Change. First Name -> First_Name NOTE: Variable Name Change. Monitoring Status -> Monitoring_Status NOTE: Variable Name Change. Address 1 -> Address_1 NOTE: Variable Name Change. Preferred Method of Contact -> Preferred_Method_of_Contact NOTE: Variable Name Change. Mobile Phone -> Mobile_Phone NOTE: Variable Name Change. Date of Last Exposure -> Date_of_Last_Exposure NOTE: Variable Name Change. End of Quarantine Period -> End_of_Quarantine_Period NOTE: Variable Name Change. Created On -> Created_On NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22763. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22774. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22799. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22899. NOTE: The import data set has 41280 observations and 20 variables. NOTE: Compressing data set ARIAS.SYSTEM_CONTACTS_PER_TRACER decreased size by 51.03 percent. Compressed is 213 pages; un-compressed would require 435 pages. NOTE: ARIAS.SYSTEM_CONTACTS_PER_TRACER data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 10.48 seconds cpu time 9.82 seconds  SAS changed the variable names.  What is lost during transcoding?   I think that there could be some extraneous characters in this data when it was exported from OPERA into Arias. Thank you for your help.   Thanks.   wlierman ... View more

Thank you for the analysis. I ran the proc import again keeping the file as an xlsx. The row count was a little higher than my "good" import. But the log shows a lot of adjustments that SAS made Proc import datafile = "I:\Health Analytics\AMB_INFORMATICS\Arias_All_Contacts_Excel_Upload\Copy 13 ! of All Sys Contacts 9-7-2020 11-41-02 AM.xlsx" 14 out = ARIAS.System_Contacts_per_tracer 15 dbms = xlsx 16 replace; 17 getnames=yes; 18 run; NOTE: Variable Name Change. (Do Not Modify) Contact -> _Do_Not_Modify__Contact NOTE: Variable Name Change. (Do Not Modify) Row Checksum -> _Do_Not_Modify__Row_Checksum NOTE: Variable Name Change. (Do Not Modify) Modified On -> _Do_Not_Modify__Modified_On NOTE: Variable Name Change. Last Name -> Last_Name NOTE: Variable Name Change. First Name -> First_Name NOTE: Variable Name Change. Monitoring Status -> Monitoring_Status NOTE: Variable Name Change. Address 1 -> Address_1 NOTE: Variable Name Change. Preferred Method of Contact -> Preferred_Method_of_Contact NOTE: Variable Name Change. Mobile Phone -> Mobile_Phone NOTE: Variable Name Change. Date of Last Exposure -> Date_of_Last_Exposure NOTE: Variable Name Change. End of Quarantine Period -> End_of_Quarantine_Period NOTE: Variable Name Change. Created On -> Created_On NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22763. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22774. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22799. WARNING: Some character data was lost during transcoding in column: Address 1 at obs 22899. NOTE: The import data set has 41280 observations and 20 variables. NOTE: Compressing data set ARIAS.SYSTEM_CONTACTS_PER_TRACER decreased size by 51.03 percent. Compressed is 213 pages; un-compressed would require 435 pages. NOTE: ARIAS.SYSTEM_CONTACTS_PER_TRACER data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 10.48 seconds cpu time 9.82 seconds  SAS changed the variable names.  What is lost during transcoding?   I think that there could be some extraneous characters in this data when it was exported from OPERA into Arias. Thank you for your help. Oh yes, how does a linefeed get embedded into data?   Thanks.   wlierman ... View more