About djbateman

djbateman · ‎01-26-2024

You are a genius. I don't think I would have figured all this out even after weeks of researching online. This saved me a ton of time. I used to be very proficient in SAS graphs, but after I left my first employer 8 years ago, I haven't used graphs much, and I have lost a lot of my skills. Thank you so much for the refresher course.

djbateman · ‎01-25-2024

From my extensive Google searching, I have not been able to find a solution to this problem. I am wondering if what I want to do is possible in SAS. I need to create a rather large graph. I basically have 3 grouping variables. The first is number of variants (0 or 1). This variable determines the bars. The second group is investigator site. This clusters the bars together. The third is country. This is a panelby variable. Below is the code that I am using (sample data attached). ods graphics / reset width=10in height=4in; proc sgpanel data=site; /* where country in ('BR','CO'); */ panelby country / onepanel layout=columnlattice colheaderpos=bottom noborder novarname; vbar siteid / group=nvariants groupdisplay=cluster grouporder=data; rowaxis display=(nolabel); colaxis display=(nolabel); run; I am running into 2 issues: When a group is not available for the group variable (NVARIANTS), it compresses the empty spaces and centers the bars. I want the zero space left blank. Notice how the tick mark for Brazil sites 016 and 018 is inbetween the 2 bars, but for sites 005 and 015, it compresses the empty bar and centers the remaining bar (no space for a zero count bar). From my understanding, there is no way to make this happen in SAS. The panelby variable (COUNTRY) creates a space for every site even if it's not available in that country. Notice that Brazil has an empty space for sites 395 and 396. Also, notice that Colombia is displaying several empty sites (because these are from Brazil and not Colombia). This is basically what I want to have happen in my first issue, but I don't want it here. I want to only display sites applicable to the associated country. Subgroup of my graph: Basically, I want to swap what is happening in both issues. I want NVARIANTS to create a spot for each possibilitiy, even if it's not there, but I want it to NOT create a spot for each site unless it is found in that country. This is what I get on my full data (very cluttered): But this is what I want the graph to look like (generated manually in Excel, butI have been asked to automate in SAS):

djbateman · ‎10-12-2023

Thank you so much! I think this did the trick. It is basically the same process as I spelled out, but yours was a bit cleaner than mine. You did yours in just a few blocks while I used several small blocks.

djbateman · ‎10-12-2023

I have an unusual task where I am given 3 lists that I merge together, and I am supposed to only report a single list of all distinct values found in all lists. Below is some code that I made up to illustrate the idea. proc sql; create table test (ID char(3), LIST1 char(10), LIST2 char(10), LIST3 char(10)); insert into test (id, list1, list2, list3) values ('001', 'A,B,C', 'A,B,C', 'A,B,C') values ('002', 'A,C,D', 'C', 'A,C,D') values ('003', 'A,B,C,D,E', '', '') values ('004', 'A,B,C,D,E', 'A,B', 'C,D,F'); quit; I am wondering if there is a simple way to end up with the following results: ID LIST 001 A,B,C 002 A,C,D 003 A,B,C,D,E 004 A,B,C,D,E,F The best I can think is to parse each item from each list into a separate variable, transpose from horizontal to vertical, remove any duplicate values, transpose from vertical to horizontal, then CATX everything again into a single list. Please tell me there is better way!

djbateman · ‎07-11-2023

Also, that was the entire log. I left nothing out. Just 3 simple notes.

djbateman · ‎07-11-2023

ARE YOU SERIOUS? That did the trick. I knew it would be something simple that I was just not seeing. Thank you so much!

djbateman · ‎07-11-2023

I am not new to PROC REPORT, but I cannot seem to get this to work for some reason. Can someone help me see where I am missing something? The code below is extrememly simplified from what I have in practice (I actually have 35+ variables between SUBJID and STATUS). But in short, I only want to highlight these 2 variables if an additional variable (NEW) is flagged even though I do not want NEW to appear in the output. I know that a compute block will only look at variables to the left, so I put NEW at the very end to ensure that all variables are previously defined. Still, when I run this code, I get a note in the log that NEW is uninitialized, and no highlighting occurrs. Can someone tell me if I am doing something wrong? proc sql; create table final (SUBJID char(11), STATUS char(15), new num); insert into final (subjid, status, new) values ('101-136-001','Screen Failed',.) values ('101-136-002','Screen Failed',1) values ('101-136-003','Screened',.) values ('101-136-004','Randomized',.) values ('101-136-005','Genotyped',1) values ('101-136-006','Screened',.); quit; proc report data=final; column SUBJID STATUS NEW; define subjid / 'Subject Number'; define status / 'Status'; define new / noprint; compute new; if new=1 then do; call define('subjid',"style","style={background=cx00B0F0}"); call define('status',"style","style={background=cx00B0F0}"); end; endcomp; run; SAS LOG: 5743 proc sql; 5744 create table final (SUBJID char(11), STATUS char(15), new num); NOTE: Table WORK.FINAL created, with 0 rows and 3 columns. 5745 insert into final (subjid, status, new) 5746 values ('101-136-001','Screen Failed',.) 5747 values ('101-136-002','Screen Failed',1) 5748 values ('101-136-003','Screened',.) 5749 values ('101-136-004','Randomized',.) 5750 values ('101-136-005','Genotyped',1) 5751 values ('101-136-006','Screened',.); NOTE: 6 rows were inserted into WORK.FINAL. 5752 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 5753 5754 proc report data=final; 5755 column SUBJID STATUS NEW; 5756 define subjid / 'Subject Number'; 5757 define status / 'Status'; 5758 define new / noprint; 5759 compute new; 5760 if new=1 then do; 5761 call define('subjid',"style","style={background=cx00B0F0}"); 5762 call define('status',"style","style={background=cx00B0F0}"); 5763 end; 5764 endcomp; 5765 run; NOTE: Variable new is uninitialized. NOTE: There were 6 observations read from the data set WORK.FINAL. NOTE: PROCEDURE REPORT used (Total process time): real time 0.03 seconds cpu time 0.03 seconds

djbateman · ‎04-26-2023

I am plotting a couple of groups on a single plot (Sirolimus and Tacrolimus), but I also need to plot some reference bands for each group. Tacrolimus is easy enough because it's the same reference regardless of how many days the subject has been on study. Sirolimus is slightly different in that the range is one range for days up to Day 90 and different for days after Day 90. The way I am plotting right now, the reference band kind of angles downward at that 90-day break (based on the observed study day just prior and just after Day 90) when I basically want it to look like two rectangles offset from each other right at x=90. Can someone help me clean up this code? Attached is a Word document with 2 graphs. The first graph is an example of what I want while the second graph is what I am getting. (Please ignore any differences outside of the bands like missing dots on the plots since I have incomplete data at the moment). Below is the code that I have developed. And if you'll notice, the red band expands the whole width of the plotting area while the blue band expands the width of the observed data. It would be nice if the blue bands could also stretch the width of the plotting area for consistency, but it's not required. proc sql; create table trough_data (CM char(25), RESULT num, STDYDAY num); insert into trough_data (cm, result, stdyday) values ('Sirolimus Trough',14.1,-1) values ('Sirolimus Trough',8.4,3) values ('Sirolimus Trough',6.8,4) values ('Sirolimus Trough',23.6,8) values ('Sirolimus Trough',17.1,13) values ('Sirolimus Trough',14.3,32) values ('Sirolimus Trough',6.6,40) values ('Sirolimus Trough',8.5,49) values ('Sirolimus Trough',11.5,68) values ('Sirolimus Trough',10.5,81) values ('Sirolimus Trough',10,89) values ('Sirolimus Trough',10.4,96) values ('Sirolimus Trough',11.6,103) values ('Sirolimus Trough',8.3,110) values ('Sirolimus Trough',12.4,116) values ('Sirolimus Trough',12,181) values ('Sirolimus Trough',8,272) values ('Tacrolimus Trough',6.5,3) values ('Tacrolimus Trough',7.1,4) values ('Tacrolimus Trough',5.4,8) values ('Tacrolimus Trough',5.2,13) values ('Tacrolimus Trough',7.4,2) values ('Tacrolimus Trough',3.2,40) values ('Tacrolimus Trough',3.7,49) values ('Tacrolimus Trough',3.2,68) values ('Tacrolimus Trough',2.9,81) values ('Tacrolimus Trough',9.8,89) values ('Tacrolimus Trough',2.9,96) values ('Tacrolimus Trough',1.8,103) values ('Tacrolimus Trough',2.9,110) values ('Tacrolimus Trough',4.2,116) values ('Tacrolimus Trough',3.1,181); quit; data trough_data; set trough_data; if stdyday<=90 then do; LOW=10; HIGH=15; end; if stdyday>=90 then do; LOW=8; HIGH=12; end; label result='ng/mL' stdyday='Study Day'; run; proc sort data=trough_data; by stdyday cm; run; options orientation=landscape; ods graphics / reset=all height=3in width=10.5in; proc sgplot data=trough_data; styleattrs datalinepatterns=(solid); series x=stdyday y=result / group=cm name='plot1' markers markerattrs=(symbol=CircleFilled); band x=stdyday lower=3 upper=6 / fillattrs=(color=red transparency=0.85); band x=stdyday lower=low upper=high / fillattrs=(color=blue transparency=0.85); keylegend 'plot1' / position=right; xaxis grid values=(-50 to 400 by 50); yaxis grid values=(0 to 25 by 5); run;

djbateman · ‎06-08-2022

I actually have more than just SUBJID as BY variables in my real process because I'm working with ConMed data. There is also a sequence ID, so a subject can have more than one record (and the date ranges could overlap). I just figured I could take whatever solution someone else has and add in the sequence variable as well. I'll play around with your solution (much simpler than what I have) and see if I can use it with my sequence variable added.

djbateman · ‎06-08-2022

I have created a process that makes a row for every date in a date range. It is very tedious and slow when working with large data. Can someone help me simply and speed up this process? As a very small scale example, suppose I have this dataset: proc sql; create table have (SUBJID char(7), STDT num format=date9., ENDT num format=date9.); insert into have (subjid, stdt, endt) values ('101-001','01JAN2022'd,'07JAN2022'd) values ('101-002','01FEB2022'd,'28FEB2022'd) values ('101-003','01JAN2022'd,'14JAN2022'd) values ('101-004','01JAN2022'd,'03JAN2022'd); quit; I want to get a record for every date within the start and end dates. So, if the subject spans 3 days, then I want to turn it into 3 records where each record contains a date within that range. This is what I am doing to get what I want: data have1; set have; REPS=endt-stdt+1; run; %macro replicate; proc sql noprint; select count(*) into :nrec trimmed from have1; select subjid, reps into :subj1-:subj&nrec., :rep1-:rep&nrec. from have1; quit; data want0; set %do i=1 %to &nrec.; %do j=1 %to &&rep&i.; have1 (where=(subjid="&&subj&i.")) %end; %end;; run; %mend replicate; %replicate; data want; set want0; by subjid; retain DATE; if first.subjid then date=stdt; else date=date+1; format date date9.; run; proc sort data=want; by subjid date; run; This process works, but it isn't very effieicent for large date ranges. I have some ranges that are up to 7000 days, so I am looping A LOT. Is there a quicker/cleaner way to do this same method that won't take so much time?

djbateman · ‎05-13-2022

Thank you. This is a good start. It will take some time to absorb it all, but thank you for pointing me in the right direction.

djbateman · ‎05-13-2022

I have done a lot of searching, and I can't find an answer that works for me. I need to see if it is possible to use SAS (not SAS EG) to copy an Excel file from a file explorer to a SharePoint site. I'm using SAS 9.4 (TS1M3). My general process is to use SAS to export a dataset to an Excel file (complete with titles, headers, and filters in place). Is it possible to then copy that Excel file to a SharePoint site? I use the X commands to copy Excel files from one folder to another as needed, but I don't think I can get it to work on a SharePoint site. On top of that, I may need to create folders on the SharePoint site to organize output over time. Does it sound possible to do what I'm explaining? If so, can someone point me to a good resource to help me get started? I can't find anything.

djbateman · ‎12-17-2021

Oh man! I swear I tried a left join. Maybe I just thought I did but never tested it because I couldn't imagine the result in my head. Thank you so much!

djbateman · ‎12-17-2021

I have 2 datasets that I need to merge, but I'm stuck in this situation because there is a repeat in the by variables group that I can't ignore. I have a dosing table that I need to merge with an external lab table, but I only want to keep where the subject and visit are found in the dosing table. So, for example below, there is dosing at Day -28 and Day 1, so I only want to keep those visits from EXT (external lab table). That's easy enough. The issue arises in the fact that at Day -28 there is only one drug administered while at Day 1 there are 2 drugs administered. In addition to that, the external types of ECG and SP only have one record per visit while SWC has 2 records per visit (due to left and right laterality). proc sql; create table DOSEF0 (SUBJID char(11), EVENT char(100), EVENTDT char(10), EXFTRT char(50), EXDTC char(16)); insert into dosef0 (subjid, event, eventdt, exftrt, exdtc) values ('102-087-201','Day -28','2021-10-06','Elexacaftor/Tezacaftor/Ivacaftor','2021-10-06T09:41') values ('102-087-201','Day 1','2021-11-03','VX-121/Tezacaftor/Deutivacaftor','2021-11-03T15:00') values ('102-087-201','Day 1','2021-11-03','Elexacaftor/Tezacaftor/Ivacaftor','2021-11-03T15:25'); create table EXT (SUBJID char(11), EVENT char(100), DATA_TYPE char(3), SWLAT char(5), EXTSTDTTM char(16), EXTENDTTM char(16)); insert into ext (subjid, event, data_type, swlat, extstdttm, extendttm) values ('102-087-201','Day -28', 'ECG','', '2021-10-06T09:06','') values ('102-087-201','Screening', 'ECG','', '2021-09-14T09:34','') values ('102-087-201','Screening', 'SWC','LEFT', '2021-09-14T09:55','2021-09-14T10:09') values ('102-087-201','Screening', 'SWC','RIGHT', '2021-09-14T10:05','2021-09-14T10:20') values ('102-087-201','Screening', 'SP', '', '2021-09-14T10:24','') values ('102-087-201','Day 1', 'ECG','', '2021-11-03T09:15','') values ('102-087-201','Day 1', 'SP', '', '2021-11-03T09:27','') values ('102-087-201','Day 1', 'SWC','LEFT', '2021-11-03T09:40','2021-11-03T15:00') values ('102-087-201','Day 1', 'SWC','RIGHT', '2021-11-03T09:47','2021-11-03T15:25') values ('102-087-201','Day 15', 'ECG','', '2021-11-18T08:49','') values ('102-087-201','Day 15', 'SP', '', '2021-11-18T09:02','') values ('102-087-201','Day 15', 'SWC','LEFT', '2021-11-18T09:13','2021-11-18T09:31') values ('102-087-201','Day 15', 'SWC','RIGHT', '2021-11-18T09:20','2021-11-18T09:45'); quit; So, from the code above, how can I get the table below? I have tried pulling in EXFTRT into EXT separately without success. I have tried different cartesian products without success. I'm just not sure how to go about this. SUBJID EVENT EVENTDT EXFTRT EXDTC DATA_TYPE SWLAT EXSTDTTM EXTENDTTM 102-087-201 Day -28 2021-10-06 Elexacaftor/Tezacaftor/Ivacaftor 2021-10-06T09:41 ECG 2021-10-06T09:06 102-087-201 Day 1 2021-11-03 VX-121/Tezacaftor/Deutivacaftor 2021-11-03T15:00 ECG 2021-11-03T09:15 102-087-201 Day 1 2021-11-03 Elexacaftor/Tezacaftor/Ivacaftor 2021-11-03T15:25 ECG 2021-11-03T09:15 102-087-201 Day 1 2021-11-03 VX-121/Tezacaftor/Deutivacaftor 2021-11-03T15:00 SP 2021-11-03T09:27 102-087-201 Day 1 2021-11-03 Elexacaftor/Tezacaftor/Ivacaftor 2021-11-03T15:25 SP 2021-11-03T09:27 102-087-201 Day 1 2021-11-03 VX-121/Tezacaftor/Deutivacaftor 2021-11-03T15:00 SWC Left 2021-11-03T09:40 2021-11-03T15:00 102-087-201 Day 1 2021-11-03 Elexacaftor/Tezacaftor/Ivacaftor 2021-11-03T15:25 SWC Right 2021-11-03T09:47 2021-11-03T15:25 102-087-201 Day 1 2021-11-03 VX-121/Tezacaftor/Deutivacaftor 2021-11-03T15:00 SWC Left 2021-11-03T09:40 2021-11-03T15:00 102-087-201 Day 1 2021-11-03 Elexacaftor/Tezacaftor/Ivacaftor 2021-11-03T15:25 SWC Right 2021-11-03T09:47 2021-11-03T15:25

djbateman · ‎07-21-2021

Okay, you have convinced me. After looking at the file structure of the 5 different domains, they are almost identical with about 1-2 variables differing. It will be much easier and trustworthy to use the data step. I was just hoping there was a magic fix out there that I was unaware of.

Online Status	Offline
Date Last Visited	4 weeks ago

How to Compare a Merge ID Variable

Re: How to know if a date from a pipe command is ddmmyyyy or mmddyyyy

Re: How to know if a date from a pipe command is ddmmyyyy or mmddyyyy

How to know if a date from a pipe command is ddmmyyyy or mmddyyyy

Re: Retain Trailing Zeros in Numeric Variables

Retain Trailing Zeros in Numeric Variables

Re: ODS EXCEL Won't Automatically Adjust Row Height

Re: ODS EXCEL Won't Automatically Adjust Row Height

Re: ODS EXCEL Won't Automatically Adjust Row Height

Re: ODS EXCEL Won't Automatically Adjust Row Height

Re: How to Compare a Merge ID Variable

Re: How to Compare a Merge ID Variable

Re: How to know if a date from a pipe command is ddmmyyyy or mmddyyyy

Re: Find Distinct Values in a Horizontal List

Re: Plotting Band Breaks in SGPLOT

Re: PROC IMPORT XLSX File and Specify Formats

Re: COMPRESS Function Modifier Warning

Force Arrow Head on HIGHLOW Plot HIGHCAP Option

Re: Converting XLSX to CSV in SAS

Convert .XPT to SAS Datasets

Re: PROC SGPANEL: Include Zero Counts for GROUP Variable, Compress Zer...

PROC SGPANEL: Include Zero Counts for GROUP Variable, Compress Zero Co...

Re: Find Distinct Values in a Horizontal List

Find Distinct Values in a Horizontal List

Re: PROC REPORT Noprint Variable in COMPUTE Block

Re: PROC REPORT Noprint Variable in COMPUTE Block

PROC REPORT Noprint Variable in COMPUTE Block

Plotting Band Breaks in SGPLOT

Re: Create a Row for Every Date within a Date Range

Create a Row for Every Date within a Date Range

Re: Upload Excel File to SharePoint within SAS

Upload Excel File to SharePoint within SAS

Re: Merge Datasets with Repeat By Variable Values

Merge Datasets with Repeat By Variable Values

Re: SAS Trying to Read a Character String as a Date - I Don't Want Tha...