I receive data files (.txt) from an external vendor.  The files contain subject IDs in the format XXX-XXX-XXX (e.g., 101-007-123).  I have noticed that when SAS reads this in using PROC IMPORT that it is trying to apply the mmddyy10. format to this field.  Because it is expecting a date string, it gives me an invalid data note for every record in the text file and then at the end gives me an unsuccessful import error (even though the data is successfully imported but with an empty subject field).   I'm wondering if there is a way to work around this.  I can ignore the invalid data notes, but the error in the log will cause a later flag that will keep later linked programs from executing.   I know that SAS provides the import code in the log, which I could copy and change the informat/format lines, but the program is standardized for different domains that have different variables, and this hardcoding would make things much more difficult to maintain.  Also, I don't even use this subject field in my program, so I don't need to have it.  Is there a way to have SAS not read in a specific field?  (I doubt it.)  Basically, I just need to know how I can get SAS to recognize SUBJECT as a character string and not a hyphenated date field.   Here is the SAS code I'm using (dummy text file attached): filename inputtxt "S:\cdm\Development\Historical\864\002\Clinical_DB\20210715_Copy\864-002-UAT-CM 20210719091521.txt" termstr=crlf; * encoding='UTF-8'; proc import out=temp datafile=inputtxt dbms=dlm replace; delimiter='09'x; getnames=yes; datarow=2; guessingrows=max; run;     Here is the log output: 1002 filename inputtxt 1002! "S:\cdm\Development\Historical\864\002\Clinical_DB\20210715_Copy\864-002-UAT-CM 1002! 20210719091521.txt" termstr=crlf; * encoding='UTF-8'; 1003 1004 proc import out=temp datafile=inputtxt dbms=dlm replace; 1005 delimiter='09'x; 1006 getnames=yes; 1007 datarow=2; 1008 guessingrows=max; 1009 run; NOTE: A byte-order mark in the file "S:\cdm\Development\Historical\864\002\Clinical_DB\20210715_Copy\864-002-UAT-CM 20210719091521.txt" (for fileref "INPUTTXT") indicates that the data is encoded in "utf-8". This encoding will be used to process the file. NOTE: A byte-order mark in the file "S:\cdm\Development\Historical\864\002\Clinical_DB\20210715_Copy\864-002-UAT-CM 20210719091521.txt" (for fileref "INPUTTXT") indicates that the data is encoded in "utf-8". This encoding will be used to process the file. 1010 /********************************************************************** 1011 * PRODUCT: SAS 1012 * VERSION: 9.4 1013 * CREATOR: External File Interface 1014 * DATE: 21JUL21 1015 * DESC: Generated SAS Datastep Code 1016 * TEMPLATE SOURCE: (None Specified.) 1017 ***********************************************************************/ 1018 data WORK.TEMP ; 1019 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 1020 infile INPUTTXT delimiter='09'x MISSOVER DSD lrecl=13106 firstobs=2 ; 1021 informat ID best32. ; 1022 informat Verbatim $11. ; 1023 informat Status $8. ; 1024 informat External_Id $139. ; 1025 informat Assigned_Type $5. ; 1026 informat Assigned_Code best32. ; 1027 informat Assigned_Text $17. ; 1028 informat Path_Code $53. ; 1029 informat Assignment_Context $9. ; 1030 informat Source $1. ; 1031 informat Score best32. ; 1032 informat Dictionary $12. ; 1033 informat Version anydtdtm40. ; 1034 informat Study $17. ; 1035 informat Subject mmddyy10. ; 1036 informat Site best32. ; 1037 informat Form $33. ; 1038 informat Route $7. ; 1039 informat Indication $9. ; 1040 informat Parent1Type $4. ; 1041 informat Parent1Code $1. ; 1042 informat Parent1Text $31. ; 1043 informat Parent2Type $4. ; 1044 informat Parent2Code $3. ; 1045 informat Parent2Text $27. ; 1046 informat Parent3Type $4. ; 1047 informat Parent3Code $4. ; 1048 informat Parent3Text $33. ; 1049 informat Parent4Type $4. ; 1050 informat Parent4Code $5. ; 1051 informat Parent4Text $37. ; 1052 informat Parent5Type $4. ; 1053 informat Parent5Code best32. ; 1054 informat Parent5Text $11. ; 1055 informat Last_Modified anydtdtm40. ; 1056 informat Creation_Date anydtdtm40. ; 1057 format ID best12. ; 1058 format Verbatim $11. ; 1059 format Status $8. ; 1060 format External_Id $139. ; 1061 format Assigned_Type $5. ; 1062 format Assigned_Code best12. ; 1063 format Assigned_Text $17. ; 1064 format Path_Code $53. ; 1065 format Assignment_Context $9. ; 1066 format Source $1. ; 1067 format Score best12. ; 1068 format Dictionary $12. ; 1069 format Version datetime. ; 1070 format Study $17. ; 1071 format Subject mmddyy10. ; 1072 format Site best12. ; 1073 format Form $33. ; 1074 format Route $7. ; 1075 format Indication $9. ; 1076 format Parent1Type $4. ; 1077 format Parent1Code $1. ; 1078 format Parent1Text $31. ; 1079 format Parent2Type $4. ; 1080 format Parent2Code $3. ; 1081 format Parent2Text $27. ; 1082 format Parent3Type $4. ; 1083 format Parent3Code $4. ; 1084 format Parent3Text $33. ; 1085 format Parent4Type $4. ; 1086 format Parent4Code $5. ; 1087 format Parent4Text $37. ; 1088 format Parent5Type $4. ; 1089 format Parent5Code best12. ; 1090 format Parent5Text $11. ; 1091 format Last_Modified datetime. ; 1092 format Creation_Date datetime. ; 1093 input 1094 ID 1095 Verbatim $ 1096 Status $ 1097 External_Id $ 1098 Assigned_Type $ 1099 Assigned_Code 1100 Assigned_Text $ 1101 Path_Code $ 1102 Assignment_Context $ 1103 Source $ 1104 Score 1105 Dictionary $ 1106 Version 1107 Study $ 1108 Subject 1109 Site 1110 Form $ 1111 Route $ 1112 Indication $ 1113 Parent1Type $ 1114 Parent1Code $ 1115 Parent1Text $ 1116 Parent2Type $ 1117 Parent2Code $ 1118 Parent2Text $ 1119 Parent3Type $ 1120 Parent3Code $ 1121 Parent3Text $ 1122 Parent4Type $ 1123 Parent4Code $ 1124 Parent4Text $ 1125 Parent5Type $ 1126 Parent5Code 1127 Parent5Text $ 1128 Last_Modified 1129 Creation_Date 1130 ; 1131 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 1132 run; NOTE: A byte-order mark in the file "S:\cdm\Development\Historical\864\002\Clinical_DB\20210715_Copy\864-002-UAT-CM 20210719091521.txt" (for fileref "INPUTTXT") indicates that the data is encoded in "utf-8". This encoding will be used to process the file. NOTE: The infile INPUTTXT is: Filename=S:\cdm\Development\Historical\864\002\Clinical_DB\20210715_Copy\864-002-UAT-CM 20210719091521.txt, RECFM=V,LRECL=52424,File Size (bytes)=2067, Last Modified=19Jul2021:09:20:00, Create Time=20Jul2021:16:35:34 NOTE: Invalid data for Subject in line 2 296-306. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9-- 2 CHAR 131.Tylenol.Exported.VX20-864-002_UAT3|United States|001|002-001-123|common_log_forms|1|comm ZONE 33305766666047767766055332333233355453756676625767677333733323332333766666656665667677376666 NUMR 131949C5EFC9580F245496820D864D002F5143C5E94540341453C001C002D001D123C3FDDFEFCF7F6F2D3C1C3FDD 93 on_log_forms|1|concomitant_medications|1|VV-000258|cmig|1|medication.TRADE.00020001005.TYLEN ZONE 66566656676773766666667667566666676667737552333333766667376666667666055444033333333333055444 NUMR FEFCF7F6F2D3C1C3FE3FD941E4FD5493149FE3C1C66D000258C3D97C1CD5493149FE942145900020001005949C5E 185 OL.ATC1:N|ATC2:N02|ATC3:N02B|ATC4:N02BE|PREF:00020001001.pgsupport.D.1.WHODrug G B3.Mar 2021 ZONE 44045433474543343374543343347454334334475544333333333333076777767704030544477624243046723333 NUMR FC91431AEC1432AE02C1433AE022C1434AE0225C0256A000200010019073500F249491978F4257070239D1202021 277 .VX20-864-002_UAT3.002-001-123.1.Prior and Concomitant Medications.Oral.Migraines.ATC1.N.NER ZONE 05533233323335545303332333233303057667266624666666766724666667666704766046676666704543040445 NUMR 96820D864D002F51439002D001D123919029F201E403FE3FD941E40D5493149FE39F21C9D97219E53914319E9E52 369 VOUS SYSTEM.ATC2.N02.ANALGESICS.ATC3.N02B.OTHER ANALGESICS AND ANTIPYRETICS.ATC4.N02BE.ANILI ZONE 54552555544045430433044444454450454304334045445244444454452444244545554544504543043344044444 NUMR 6F53039345D914329E0291E1C753933914339E0229F485201E1C75393301E401E4909254933914349E022591E9C9 461 DES.PREF.00020001001.PARACETAMOL.16/Jul/2021 08:11:12.16/Jul/2021 08:10:56 534 ZONE 44505544033333333333054544454444033247623333233333333033247623333233333333 NUMR 45390256900020001001901213541DFC916FA5CF2021008A11A12916FA5CF2021008A10A56 ID=131 Verbatim=Tylenol Status=Exported External_Id=VX20-864-002_UAT3|United States|001|002-001-123|common_log_forms|1|common_log_forms|1|conc omitant_medications|1|VV-000258|cmig|1|medication Assigned_Type=TRADE Assigned_Code=20001005 Assigned_Text=TYLENOL Path_Code=ATC1:N|ATC2:N02|ATC3:N02B|ATC4:N02BE|PREF:00020001001 Assignment_Context=pgsupport Source=D Score=1 Dictionary=WHODrug G B3 Version=01MAR21:00:00:00 Study=VX20-864-002_UAT3 Subject=. Site=1 Form=Prior and Concomitant Medications Route=Oral Indication=Migraines Parent1Type=ATC1 Parent1Code=N Parent1Text=NERVOUS SYSTEM Parent2Type=ATC2 Parent2Code=N02 Parent2Text=ANALGESICS Parent3Type=ATC3 Parent3Code=N02B Parent3Text=OTHER ANALGESICS AND ANTIPYRETICS Parent4Type=ATC4 Parent4Code=N02BE Parent4Text=ANILIDES Parent5Type=PREF Parent5Code=20001001 Parent5Text=PARACETAMOL Last_Modified=16JUL21:08:11:12 Creation_Date=16JUL21:08:10:56 _ERROR_=1 _N_=1 NOTE: Invalid data for Subject in line 4 300-310. 4 CHAR 134.Advil.Exported.VX20-864-002_UAT3|United States|001|002-001-888|common_log_forms|1|common ZONE 33304676604776776605533233323335545375667662576767733373332333233376666665666566767737666666 NUMR 13491469C9580F245496820D864D002F5143C5E94540341453C001C002D001D888C3FDDFEFCF7F6F2D3C1C3FDDFE 93 _log_forms|1|concomitant_medications|1|VV-000258|cmig|1|medication.TRADE.00109201030.ADVIL [ ZONE 56665667677376666666766756666667666773755233333376666737666666766605544403333333333304454425 NUMR FCF7F6F2D3C1C3FE3FD941E4FD5493149FE3C1C66D000258C3D97C1CD5493149FE94214590010920103091469C0B 185 IBUPROFEN].ATC1:C|ATC2:C01|ATC3:C01E|ATC4:C01EB|PREF:00109201001.islack.D..WHODrug G B3.Mar ZONE 44555444450454334745433433745433433474543343344755443333333333330676666040054447762424304672 NUMR 92502F65ED91431A3C1432A301C1433A3015C1434A30152C0256A00109201001993C13B949978F4257070239D120 277 2021.VX20-864-002_UAT3.002-001-888.1.Prior and Concomitant Medications.Oral.Rash.ATC1.C.CARD ZONE 33330553323332333554530333233323330305766726662466666676672466666766670476605676045430404454 NUMR 202196820D864D002F51439002D001D888919029F201E403FE3FD941E40D5493149FE39F21C92138914319393124 RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9-- 369 IOVASCULAR SYSTEM.ATC2.C01.CARDIAC THERAPY.ATC3.C01E.OTHER CARDIAC PREPARATIONS.ATC4.C01EB.O ZONE 44545454452555544045430433044544442544545504543043340454452445444425545454544450454304334404 NUMR 9F61335C12039345D914329301931249130485210991433930159F4852031249130025012149FE3914349301529F 461 THER CARDIAC PREPARATIONS.PREF.00109201001.IBUPROFEN.19/Jul/2021 11:36:45.16/Jul/2021 08:10: ZONE 54452445444425545454544450554403333333333304455544440332476233332333333330332476233332333333 NUMR 4852031249130025012149FE390256900109201001992502F65E919FA5CF2021011A36A45916FA5CF2021008A10A 553 56 554 ID=134 Verbatim=Advil Status=Exported External_Id=VX20-864-002_UAT3|United States|001|002-001-888|common_log_forms|1|common_log_forms|1|conc omitant_medications|1|VV-000258|cmig|1|medication Assigned_Type=TRADE Assigned_Code=109201030 Assigned_Text=ADVIL [IBUPROFEN] Path_Code=ATC1:C|ATC2:C01|ATC3:C01E|ATC4:C01EB|PREF:00109201001 Assignment_Context=islack Source=D Score=. Dictionary=WHODrug G B3 Version=01MAR21:00:00:00 Study=VX20-864-002_UAT3 Subject=. Site=1 Form=Prior and Concomitant Medications Route=Oral Indication=Rash Parent1Type=ATC1 Parent1Code=C Parent1Text=CARDIOVASCULAR SYSTEM Parent2Type=ATC2 Parent2Code=C01 Parent2Text=CARDIAC THERAPY Parent3Type=ATC3 Parent3Code=C01E Parent3Text=OTHER CARDIAC PREPARATIONS Parent4Type=ATC4 Parent4Code=C01EB Parent4Text=OTHER CARDIAC PREPARATIONS Parent5Type=PREF Parent5Code=109201001 Parent5Text=IBUPROFEN Last_Modified=19JUL21:11:36:45 Creation_Date=16JUL21:08:10:56 _ERROR_=1 _N_=3 NOTE: 3 records were read from the infile INPUTTXT. The minimum record length was 534. The maximum record length was 588. NOTE: The data set WORK.TEMP has 3 observations and 36 variables. NOTE: DATA statement used (Total process time): real time 0.11 seconds cpu time 0.12 seconds Errors detected in submitted DATA step. Examine log. 3 rows created in WORK.TEMP from INPUTTXT. ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.21 seconds cpu time 0.20 seconds   ... View more

I have found an issue that I have resolved, but I wanted someone to explain to me why it might be happening and if there is a workaround.   In general, we store a log file for every program we run.  After the program runs, we run the log file through another macro that searches for errors/warnings and returns a report informing us if there are issues in the log.  I found an instance where the log report claimed that there were no issues, but the expected output file was never created.  When I manually searched the log, I found that there was a single error at the bottom of the log file.  The log file has 3153 rows, but when I examined the log summary macro, I found that only 2261 lines were being read in.   This is line 2261 where SAS stops reading in the log file:     %createcheck(chk=102, whr=%str(exftrt='Elexacaftor/Tezacaftor/Ivacaftor' & timepart(dosetm)>='15:00't), keepvars=&idvars. visitmnemonic exftrt dosedttm comment resolved, message=%str(Study drug dose time is expected to be in the morning));     I found that the log is translating the ">=" into a right-pointing arrow.  Further, the ")," after denoting that a character string as a time value is translated into an Extra-High Tone Bar (unicode ID: U+02E5).  When I attached the log file, and those special characters disappear, so below is a screen shot of lines 2259-2267.     I'm assuming that the arrow is telling SAS that it found the end of a file and stopped reading in more data because I fixed the code by changing ">=" to "ge", and the code ran fine with the log report detecting the error message at the bottom of the page.  It seems that the tone bar is not influencing the log report at all.   My concern is that I have never used "ge" before but have always used ">=".  I'm concerned about how many times in my log reports issues were not detected because the file was prematurely stopped when reading it in.  This is especially concerning because line 2267 in the screenshot above does not translate ">=" into an arrow.  Has anyone else had to deal with this problem?  Is there a way to deal with it without going through all programs that I am running regularly (literally hundreds of program files) and searching them all for ">="? ... View more

I have an unusual assignment where I have a list of expected lab tests categorized by "Sample Type", "Sample Name", and "Temperature".  I then receive a file from the lab denoting what lab samples were received with its temperature.  I am supposed to detect if anything in the lab file is not found in the expected list.  That part would be easy to just merge by the 3 variables.  The hard part comes in when they want me to detail which variable was the cause of not finding a match.   For example, the SAS code below shows a small sample of expected labs and a small sample of received labs.  Because there are some "typos" or a different sample received, I want to retain those mismatches, but also show why they mismatched.  So, in the LAB_FILE dataset, rows 3, 6, 7, and 8 should be retained.  However, I am not sure how to denote which variable caused it to not be found.  Line 3 should return unexpected Type and Name.  Line 6 should return unexpected Name.  Line 7 should return unexpected Type.  Line 8 should return unexpected Temperature.   I have noticed that when I can't figure out how to do something, someone always comes to my rescue with SQL, so maybe I just need to use a different approach.  Does anyone have any helpful ideas?   data list; input Sample_Type $ 1-16 Sample_Name $ 17-28 Temperature $ 29-36; datalines; Plasma EDTA K2 BIO PLSM 1 Frozen Plasma EDTA K2 BIO PLSM 2 Frozen WB EDTA K2 139 VARIANT Frozen WB EDTA K2 39 VARIANT Frozen WB EDTA K2 FLSEQ Frozen WB EDTA K2 HEMOR Ambient ; run; data lab_file; input Sample_Type $ 1-16 Sample_Name $ 17-28 Temperature $ 29-36; datalines; Plasma EDTA K2 BIO PLSM 1 Frozen Plasma EDTA K2 BIO PLSM 2 Frozen Plasma EDTA K3 BIO PLSM 3 Frozen WB EDTA K2 139 VARIANT Frozen WB EDTA K2 39 VARIANT Frozen WB EDTA K2 29 VARIANT Frozen WB EDTA HEMOR Ambient WB EDTA K2 FLSEQ Ambient ; run; proc sort data=list; by sample_type sample_name temperature; proc sort data=lab_file; by sample_type sample_name temperature; run; /* I want to keep rows 3, 6, 7, 8 from LAB_FILE but denote which variable causes a mismatch */ data compare; merge list (in=a) lab_file (in=b); by sample_type sample_name temperature; if a & b then delete; if b; run;   ... View more

My company used to always use PROC IMPORT to read in an Excel file, but sometimes we have run into the issue where SAS will not allow a file path + file name to be more than 201 characters long.  We thought it would be wise to use the LIBNAME statement with the XLSX engine.  I have attached a screenshot of my code and the DTS library that is created.  My problem is that files are not always created with standard SAS naming conventions.  In other words, a tab is named "DATASET VARIABLE ATTRIBUTES", but SAS cannot recognize this as a dataset due to the spaces, so it is inaccessible.  I know that when SAS reads in an Excel file and the column names are not SAS standard, it will make them standard by replacing spaces, commas, hyphens, etc. with underscore "_".  Is there a way for SAS to do this with the tab names in an Excel file?  I'm just wondering if this method of importing an Excel file will truly work for us.   Excel File Tab Names     SAS Code and DTS Library Dataset Names ... View more

I need someone to explain to me what is happening in the background in a compute block.   I am creating a report where I am comparing dates from 2 different reports.  If there is a mismatching date, then I concatenate them.  For example, you can see that the Baseline_Start_Date and Baseline_End_Date did not match between CDM and SM reports, so they are concatenated.   Baseline_Start_Date Baseline_End_Date Actual_Start_Date Actual_End_Date 2023-06-30 (CDM) / 2020-10-20 (SM) 2023-06-30 (CDM) / 2020-10-20 (SM)       My task is to highlight Baseline_Start_Date when Actual_Start_Date is blank and Baseline_Start_Date is mismatching.  I programatically determine if the dates are not matching if the length of the date field is greater than 20.  Likewise, I need to highlight Baseline_End_Date when Actual_End_Date and Baseline_End_Date is mismatching.  I'm trying to use a compute block, but I'm not sure how this works when using multiple variables in an IF statement.  With the code below, it will highlight only when Baseline_Start_Date > 20 regardless of if Actual_Start_Date is blank or not.  How do I incorporate the Actual_Start_Date condition as well?   compute Baseline_Start_Date; if (missing(Actual_Start_Date)) & length(Baseline_Start_Date) > 20 then do; call define('Baseline_Start_Date', "style", "style=[background=yellow]"); end; endcomp;   When I specify a compute block, which variable should be placed there?  I assumed Baseline_Start_Date because that is the variable that I want to conditionally highlight.  But then inside the block, the condition is based on 2 variables (Baseline_Start_Date and Actual_Start_Date).  Can someone explain to me how compute blocks work with multiple variables involved? ... View more