I'm using prxmatch to match the string "END OF LIFE" but I don't want that string to match if it is followed by " CARE".  However, I can't seem to get the regular expression right (see below).  I could find lots of examples for excluding single characters but not for strings.   The regular expression should not match on "END OF LIFE CARE", but should match on "END OF LIFE MEDICATIONS" and "END OF LIFE OPTION" and "NEAR END OF LIFE OPTION"   data out; set in; if prxmatch('/(END OF LIFE[^( CARE)])/i',strip(string)) then output; run;   Thanks. ... View more

I'm querying an Oracle database and need to use SQL.  I'm trying to pull records from the oracle database where the year of birth is either 2019 or 2020 AND (match either DOB OR first name OR last name OR mother's last) from records in a SAS dataset.  When I do this I get duplicate records (in the millions) when I'd like to get only one distinct record even when the same record meets the where criteria for multiple records in the SAS dataset.  Any help is appreciated. proc sql; create table OUTPUT as select a.ID as CERTIFICATEID label='CERTIFICATEID', a.BIRTH_ID, a.SEALED, a.CREATE_DATE, datepart(a.BIRTH_DATE) as B_DOB label='B_DOB' format=mmddyy10., a.CHILD_SEX as B_SEX label='B_SEX', a.CHILD_NAME_FIRST as B_NAME_FIRST label='B_NAME_FIRST', a.CHILD_NAME_MIDDLE as B_NAME_MIDDLE label='B_NAME_MIDDLE', a.CHILD_NAME_LAST as B_NAME_LAST label='B_NAME_LAST', a.PARENT_1_NAME_FIRST as B_NAME_FIRST_FATHER label='B_NAME_FIRST_FATHER', a.PARENT_1_NAME_MIDDLE as B_NAME_MIDDLE_FATHER label='B_NAME_MIDDLE_FATHER', a.PARENT_1_NAME_LAST as B_NAME_LAST_FATHER label='B_NAME_LAST_FATHER', a.PARENT_2_NAME_FIRST as B_NAME_FIRST_MOTHER label='B_NAME_FIRST_MOTHER', a.PARENT_2_NAME_MIDDLE as B_NAME_MIDDLE_MOTHER label='B_NAME_MIDDLE_MOTHER', a.PARENT_2_NAME_LAST as B_NAME_LAST_MOTHER label='B_NAME_LAST_MOTHER' from ORACLE.TABLE as a, SAS_DATASET as e where year(datepart(a.BIRTH_DATE)) in (2020 2019) /* Do not include if not state registered*/ AND ( a.CHILD_NAME_FIRST=e.D_NAME_FIRST OR a.CHILD_NAME_LAST=e.D_NAME_LAST OR put(datepart(a.BIRTH_DATE),mmddyy10.)=e.D_DOB OR a.PARENT_2_NAME_FIRST=e.D_NAME_FIRST_MOTHER ) ; quit; ... View more

I have a comma-delimited data file that includes names with apostrophes (i.e., single quotes).  This causes multiple fields to be read incorrectly into a single field.  For example, there have been rare instances in which the first name begins with an apostrophe and the last name ends with an apostrophe causing the first, middle, and last names to read in all together as the first name (e.g., 'LARRY,JOE,BIRD').  Is there an option that disallows single quotes to be used to enclose text with embedded commas but still allows double quotes to enclose text with embedded commas?  Single quotes are allowed at data entry but double quotes are not.   Thanks!   data TEST_QUOTE_IMPORT; infile "C:\TEMP\TEST_QUOTE.csv" delimiter =',' MISSOVER DSD lrecl=32767 firstobs=1 ; informat ID $13. ; informat ID_FLAG $1. ; informat ID_ALT $13. ; informat CHILD_BIRTH_STATE_4 $50. ; informat SEQUENCE $6. ; informat NAME_FIRST $60. ; informat NAME_MIDDLE_7 $60. ; informat NAME_LAST_8 $60. ; input ID $ ID_FLAG $ ID_ALT $ SEQUENCE $ NAME_FIRST_6 $ NAME_MIDDLE_7 $ NAME_LAST_8 $ ; run; ... View more

I'm trying to extract a subset of records from a fixed-width data file using an infile statement and looking for the substring '2018' at a specific location.  My goal is to output the matching records into the same fixed-width file format from which they were extracted.  However, the original file contains records in a fixed-width 4000 character string with the last 989 characters as padded spaces.  No matter what I do I can't seem to retain the padded spaces at the end.  The resulting data file (temp.txt) has records that end at position 3019.  How do I retain the padded spaces in the new file?   To be clear, I can import both the original and created fixed-width files into SAS using the same import code.  That's not my goal.  My goal is to retain the padded spaces so the file will be recognized as the acceptable file format by the system to which it is submitted.   Thanks!   data test; missing; infile "C:\TEMP\fixed_width_4000.txt" lrecl=4000 truncover; input lines $1-4000; if substr(lines,1,4) eq '2018'; run; Options noQuoteLenMax; data _null_; set test; file "C:\TEMP\temp.txt" lrecl=4000 n=4 MOD; put lines; run;   ... View more

%macro import_DD(In_DD,Out_DD); PROC IMPORT OUT= WORK.&Out_DD._raw DATAFILE= "C:\Projects\Data Dictionary_with_variable_names.xlsx" DBMS=EXCEL REPLACE; RANGE="'&In_DD.$'"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %let import_DD_&Out_DD._err=&SYSERR; %put &import_DD_&Out_DD._err; %mend import_DD; %import_DD(DATA DICTIONARY,DD); %import_DD(APPENDIX A1-CDPH State Codes,A1_DD); %import_DD(APPENDIX A2- MARC (Country),A2_DD); %import_DD(APPENDIX B-Country FIPS Codes,B_DD); %import_DD(APPENDIX C_State FIPS Codes,C_DD); %import_DD(APPENDIX D-Race Codes,D_DD); %import_DD(APPENDIX G-County Codes,G_DD); %import_DD(APPENDIX H-VS 10A,H_DD); %import_DD(APPENDIX I-HOSPITAL CODES,I_DD); The code above imports a data dictionary and appendices listed on different tabs in an Excel file.  I'd like to assign &SYSERR after each import to a new macro variable so I can track whether or not there were errors during import, but I'm struggling to find code that will allow me to use the macro variable assigned at the top of the macro to name the new macro used to capture error codes.  Any assistance is appreciated.  Thanks! ... View more

We have a data file in csv format and there's a datetime field in mm/dd/yyyy hh:mm:ss (24 hour time format) for which I'm having trouble importing.  Although this seems like a standard datetime format, surprisingly I can't seem to find an informat that works.  If the time was in the AM/PM format I could use ANYDTDTM or MDYAMPM** but the 24 hour time format appears to be problematic.  I'm writing specifications for data users and I'd like it to be as simple as possible (i.e., creating user-defined informat is not an option) and all other date formats are in the mm/dd/yyyy format and all times are in the hh:mm format (24 hour), so I'd like to keep to those standards if possible.  Is there an informat for this I just haven't found yet?   Thanks! Ryan     ... View more