I have a situation where I have a parent study and a roll-over study.  I need to merge any AE records from the parent study that are missing an end date with all AEs in the roll-over study to see if the parent AEs got entered into the roll-over study.  I'm having issues due to how data is entered.   I want to merge by SUBJECT, LLT_NAME, and AESTDAT.  However, sometimes, LLT_NAME is missing.  In those cases, I want to merge on PARAEID (Note: PARAEID is in the roll-over study and matches AESPID in the parent study).  I need to find a way to look ahead and see if I should merge by LLT_NAME or PARAEID on an individual record level.   I was doing something like:   data ae_parent; set ae_parent; if llt_name^='' then mergevar=strip(llt_name); else if aespid^=. then mergevar=compress(put(aespid,best.)); run; data ae_ro; set ae_ro; if llt_name^='' then mergevar=strip(llt_name); else if paraeid^=. then mergevar=compress(put(paraeid,best.)); run;   The downfall is that MERGEVAR will not always properly match.  If the parent study has LLT_NAME and the roll-over study does not, then MERGEVAR will be LLT_NAME in the parent study and PARAEID (or blank if PARAEID is also missing) in the roll-over study.   If you look at my full sample code below, you'll see in the final output (AE_MERGED) that there are 6 records with a status of "NOT Copied Over" when only the last 2 of those 6 should have that status because the other 4 records are creating different values for MERGEVAR.  So, is there any way that someone can help me find an algorithm for properly matching up based on differing variables?     data ae_parent; input SUBJECT $11. LLT_NAME $34. AESPID 49-50 AESTDAT date9.; format AESTDAT date9.; aestdat=mdy(month(aestdat),day(aestdat),2018); /* AESTDAT reading in as 2020. Forcing to 2018. */ cards; 103-049-001 CRP increased 2 09MAY2018 103-049-001 Bacteria urine identified 7 21JUN2018 103-049-001 Urine WBC increased 8 21JUN2018 103-049-001 Urine epithelial cells increased 6 21JUN2018 103-049-001 Urine leukocyte esterase positive 9 21JUN2018 103-049-003 APTT increased 3 02MAY2018 103-049-003 INR increased 2 02MAY2018 103-049-003 PT increased 1 02MAY2018 103-049-003 Nasal mucus increased 5 19JUN2018 103-049-004 3 02JUL2018 103-049-004 APTT increased 9 17JUL2018 103-049-004 Blood glucose increased 6 17JUL2018 103-049-004 Decreased hemoglobin 10 17JUL2018 103-049-004 Glucose urine increased 11 17JUL2018 103-049-004 INR increased 8 17JUL2018 103-049-004 PT increased 7 17JUL2018 ; run; data ae_ro; input SUBJECT $11. LLT_NAME $34. AESPID 49-50 PARAEID 53-54 AESTDAT date9.; format AESTDAT date9.; aestdat=mdy(month(aestdat),day(aestdat),2018); /* AESTDAT reading in as 2020. Forcing to 2018. */ cards; 103-049-001 CRP increased 3 2 09MAY2018 103-049-001 5 7 21JUN2018 103-049-001 6 8 21JUN2018 103-049-001 Urine epithelial cells increased 4 6 21JUN2018 103-049-001 Urine leukocyte esterase positive 7 9 21JUN2018 103-049-001 Skin irritation 1 24JUL2018 103-049-001 Skin irritation 2 22AUG2018 103-049-003 4 02MAY2018 103-049-003 INR increased 3 2 02MAY2018 103-049-003 PT increased 2 1 02MAY2018 103-049-003 Nasal mucus increased 5 5 19JUN2018 103-049-003 Upper respiratory infection 1 30SEP2018 103-049-004 Creatine kinase increased 6 3 02JUL2018 103-049-004 APTT increased 10 9 17JUL2018 103-049-004 Blood glucose increased 7 6 17JUL2018 103-049-004 INR increased 9 8 17JUL2018 103-049-004 PT increased 8 7 17JUL2018 103-049-004 Ear pain 1 29JUL2018 103-049-004 Creatinine increased 2 31JUL2018 103-049-004 AST increased 3 11SEP2018 103-049-004 Creatine kinase increased 4 11SEP2018 103-049-004 Upper respiratory infection 5 06OCT2018 ; run; data ae_parent; set ae_parent; if llt_name^='' then mergevar=strip(llt_name); else if aespid^=. then mergevar=compress(put(aespid,best.)); run; data ae_ro; set ae_ro; if llt_name^='' then mergevar=strip(llt_name); else if paraeid^=. then mergevar=compress(put(paraeid,best.)); run; proc sort data=ae_parent; by subject mergevar aestdat; proc sort data=ae_ro; by subject mergevar aestdat; run; data ae_merged; merge ae_parent (in=a) ae_ro (in=b); by subject mergevar aestdat; if a; if b=0 then STATUS='NOT Copied Over'; if b=1 then STATUS='Copied Over'; run;   ... View more

Can someone explain to me why I'm getting the warning that I'm getting?  I've tried reading other posts and can't find a solution.   At various times throughout a program, I am passing a parameter that is typically 2-6 characters plus a 3-digit number through a macro.  In the macro, I am trying to extract just the digits.  I'm trying to use the A modifier in the compress() function, but I get a warning that appears twice with every pass of the macro:   WARNING: In a call to the COMPRESS function or routine, the modifier "'" not valid.   The warning isn't influencing the output (as you can see in the log), but I still want to understand the warning:   6968 %macro check (dsin=); 6969 %let checkval=%sysfunc(compress(&dsin.,'','a')); 6970 %put CHECKVAL=~~~&checkval.~~~; 6971 %mend check; 6972 6973 6974 %check (dsin=AE001); WARNING: In a call to the COMPRESS function or routine, the modifier "'" not valid. WARNING: In a call to the COMPRESS function or routine, the modifier "'" not valid. CHECKVAL=~~~001~~~ Here is a very small portion of the macro I am running with various test cases for the input parameter:   %macro check (dsin=); %let checkval=%sysfunc(compress(&dsin.,'','a')); %put CHECKVAL=~~~&checkval.~~~; %mend check; %check (dsin=AE001); %check (dsin=AE002); %check (dsin=AE003); %check (dsin=AE004); %check (dsin=AE005); %check (dsin=CFAE003); %check (dsin=AE003); %check (dsin=AE004); %check (dsin=AE011); %check (dsin=BF012); %check (dsin=ZQ014); %check (dsin=LKNM016);   ... View more

To decrease file sizes, my company has standard code that exports files to XML format and then uses %convert_files() macro to convert XML to XLSX.  Basically, this macro will go to the specified directory (default parameter) and convert all XML files to the specified file type (ext parameter).   I run into a small issue where a SAS program might produce a "corrupt" file.  This is detected by the file size being 68 KB (a blank but non-corrupted file is 70 KB).  When such a file is found, this macro pauses and won't convert the files.  Even if later down the road this file is still in the directory and a new non-corrupted file is produced, it won't convert even the valid files.   I don't know VBscript, so can anyone tell me if I can modify the code below to delete any XML files of size 68 KB found before running through the directory and converting any other files?   options noxwait noxsync; %macro convert_files(default=,ext=); data _null_; file "'&default\temp.vbs'"; put "set xlapp = CreateObject(""Excel.Application"")"; put "set fso = CreateObject(""scripting.filesystemobject"")"; put "set myfolder = fso.GetFolder(""&default"")"; put "set myfiles = myfolder.Files"; put "xlapp.DisplayAlerts = False"; put " "; put "for each f in myfiles"; put " ExtName = fso.GetExtensionName(f)"; put " Filename= fso.GetBaseName(f)"; put " if ExtName=""&ext"" then"; put " set mybook = xlapp.Workbooks.Open(f.Path)"; put " xlapp.Visible = false"; put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51"; put " End If"; put " Next"; put " mybook.Close"; put " xlapp.DisplayAlerts = True"; /* Removes original files */ put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly"; put " xlapp.Quit"; put " Set xlapp = Nothing"; put " strScript = Wscript.ScriptFullName"; put " FSO.DeleteFile(strScript)"; run; x "cscript ""&default\temp.vbs"""; %mend; ... View more

I am trying to find the first date of the latest regimen in a group of treatments.  I'm having a hard time wrapping my head around this.  I have a feeling PROC SQL with a HAVING statement might be my best bet, but I'm not able to think of how to do this.   Suppose I have several subjects who have received various regimens of therapy where each regimen can contain multiple therapies.  I want to find the latest date for each subject.  Then, I need to find the earliest date in that corresponding regimen.   So, in the data below, Subject 101-001 has a latest date of 04JUL2014, which is Regimen #2.  The earliest date in Regimen #2 is 10JUN2014.  That would be the record I want to keep.  Likewise, Subject 101-002 has a latest date of 21DEC2016, which is in Regimen #2.  The earliest date in Regimen #2 is 15NOV2016.  That would be the record I would want to keep.   I can't make any assumptions like regimens being in chronological order.  It could very well happen that Regimen #2 happens a couple years before Regimen #1.  Regimens are the order they are entered more so than when they occurred.   Subject Regimen Date 101-001 1 12-Dec-2013 101-001 1 21-Dec-2013 101-001 1 5-Jan-2014 101-001 2 10-Jun-2014 101-001 2 19-Jun-2014 101-001 2 4-Jul-2014 101-002 1 27-Sep-2016 101-002 1 1-Oct-2016 101-002 2 15-Nov-2016 101-002 2 21-Dec-2016 101-002 3 20-Nov-2016 101-002 3 1-Dec-2016 ... View more

I need help with transposing some datasets.  I have multiple datasets that I need transposed by some ID variables.   Below is a sample of one dataset and the transpose procedure I am attempting to use.   data DSIS2; input STUDYID $12. +2 SITEID $3. +2 SUBJID $11. +2 STDYPRD 1. +2 PNINTSLP 1. +2 OPENDATE date9. +2 OPENTIME time5. +2 SAVEDATE date9. +2 SAVETIME time5.; format OPENDATE SAVEDATE date9. OPENTIME SAVETIME time5.; cards; VX16-150-102 022 102-022-002 1 4 12DEC2017 10:19 12DEC2017 10:19 VX16-150-102 022 102-022-002 1 5 13DEC2017 10:01 13DEC2017 10:01 VX16-150-102 022 102-022-002 1 8 14DEC2017 10:01 14DEC2017 10:01 VX16-150-102 022 102-022-002 1 5 15DEC2017 10:01 15DEC2017 10:02 VX16-150-102 022 102-022-002 1 7 16DEC2017 10:00 16DEC2017 10:01 VX16-150-102 022 102-022-002 1 4 17DEC2017 10:00 17DEC2017 10:00 VX16-150-102 022 102-022-002 1 6 18DEC2017 10:01 18DEC2017 10:01 VX16-150-102 022 102-022-002 1 7 19DEC2017 05:56 19DEC2017 05:56 ; run; proc transpose data=DSIS2 out=DSIS2_T; by studyid siteid subjid; var stdyprd pnintslp opendate opentime savedate savetime; run;     My goal is to get something that looks like this (spacing between groups just for demonstration--I don't actually need a blank line between groups):     VX16-150-102 022 102-022-002 STDYPRD 1 VX16-150-102 022 102-022-002 PNINTSLP 4 VX16-150-102 022 102-022-002 OPENDATE 12DEC2017 VX16-150-102 022 102-022-002 OPENTIME 10:19 VX16-150-102 022 102-022-002 SAVEDATE 12DEC2017 VX16-150-102 022 102-022-002 SAVETIME 10:19 VX16-150-102 022 102-022-002 STDYPRD 1 VX16-150-102 022 102-022-002 PNINTSLP 5 VX16-150-102 022 102-022-002 OPENDATE 13DEC2017 VX16-150-102 022 102-022-002 OPENTIME 10:01 VX16-150-102 022 102-022-002 SAVEDATE 13DEC2017 VX16-150-102 022 102-022-002 SAVETIME 10:01 VX16-150-102 022 102-022-002 STDYPRD 1 VX16-150-102 022 102-022-002 PNINTSLP 6 VX16-150-102 022 102-022-002 OPENDATE 14DEC2017 VX16-150-102 022 102-022-002 OPENTIME 10:01 VX16-150-102 022 102-022-002 SAVEDATE 14DEC2017 VX16-150-102 022 102-022-002 SAVETIME 10:01 VX16-150-102 022 102-022-002 STDYPRD 1 VX16-150-102 022 102-022-002 PNINTSLP 5 VX16-150-102 022 102-022-002 OPENDATE 15DEC2017 VX16-150-102 022 102-022-002 OPENTIME 10:01 VX16-150-102 022 102-022-002 SAVEDATE 15DEC2017 VX16-150-102 022 102-022-002 SAVETIME 10:02 etc...     When you open DSIS2_T, you'll see that I actually get a new column for each row (which I understand is what transposing does).  But I basically want a stacked transposition.  Is there a shortcut way to do this rather than looping through and setting the dataset and only keeping the ID variables and the new variable of interest?   As an added caveat, notice that the date and time variables lose their formatting.  I want to keep the formatting.  Also, I have several datasets that I am trying to transpose and then stack on top of each other.  In this case, all the variables are numeric, so they come out as numeric columns, but some datasets may contain character variables, in which case I want to make all the columns character.  Is this possible in a shortened way?   My current process is: Get a list of all the datasets I want transposed Loop through each dataset (m times) Get a list of all the variables I want stacked from dataset[m] Loop through each variable (n times) Determine the variable[n] type Using a SET statement, stack dataset[m] n times keeping only the ID variables and variable[n] for each set but renaming a character variable to CHAR and a numeric variable to NUM to create TABLE[m] Make all numeric variables character using PUT() Stack all TABLE[m] into a single table   There must be a simpler way than this!   ... View more

I have an unknown number of variables that have the same prefix for the variable name.  I want to concatenate all the fields into a single field separated by a semi-colon.  Is there a simple notation to do something like:   catx('; ', code:) I could create a macro variable that gets the number of variables and then just do:   catx('; ', of code1-code&ncodes.) but I thought I would see if there is a simpler notation. ... View more