Hi everyone, I have a data set with subjects and their various visits. Some of the visits have a bin/batch date under which previous visits fall (e.g., subject ID 4 has 4 batches). My goal is to create a data set with variables that list the various visits in their respective batches. Obviously, I'm a SAS beginner and have hammered together some code that only partially works. I'd be very grateful for your help on getting this code working! Thank you so much in advance! Here's a screenshot of Have and Want: In Want, I'd like there to be a 'Yes' only if a visit falls in a certain batch (my code currently can't do that). I'd also like for the batch dates to be carried through (as opposed to the 'Yes') but only for visits that were part of a batch. In addition, there should be a variable called 'Batch?' that simply says Yes or No depending on whether a visit is part of a batch or not. Here's my try at it: data have;
infile datalines delimiter=' ';
input ID Visit $ Date date11. Binday$;
datalines;
1 Week3 21-Dec-2018 No
1 Week6 19-Feb-2019 No
1 Week10 14-May-2019 No
1 Week14 06-Aug-2019 No
1 Week18 30-Oct-2019 No
1 Week22 28-Jan-2020 No
2 Week3 25-Jan-2019 No
2 Week6 26-Mar-2019 No
2 Week10 19-Jun-2019 No
2 Week14 10-Sep-2019 No
2 Week18 04-Dec-2019 Yes
3 Week3 30-Jan-2019 Yes
4 Week3 04-Oct-2017 No
4 Week6 06-Dec-2017 No
4 Week10 28-Feb-2018 Yes
4 Week14 23-May-2018 No
4 Week18 14-Aug-2018 No
4 Week22 07-Nov-2018 Yes
4 Week26 30-Jan-2019 No
4 Week30 24-Apr-2019 No
4 Week34 17-Jul-2019 No
4 Week38 09-Oct-2019 Yes
4 Week40 02-Jan-2020 No
4 Week42 25-Mar-2020 No
4 Week44 17-Jun-2020 No
4 Week46 09-Sep-2020 Yes
4 Week48 03-Dec-2020 No
4 Week50 24-Feb-2021 No
5 Week8 16-Mar-2018 Yes
5 Week11 07-May-2018 No
5 Week14 10-Jul-2018 Yes
;
run;
/* Create an enumeration variable of changes in Binday for each ID */
data work.want1;
do _n_=0 by 1 until(last.ID);
do until(last.Binday);
set work.have;
by ID Binday notsorted;
Change=_n_;
output;
end;
end;
run;
/* Translate changes in Binday to batch numbers */
/* First, remove all No visits */
data work.want2;
set work.want1;
if Binday ne 'No';
run;
/* Second, enumerate again through the Yes visits */
data work.want3;
do _n_=1 by 1 until(last.ID);
do until(last.Change);
set work.want2;
by ID Change notsorted;
NewChange=_n_;
output;
end;
end;
run;
/* Create a new batch variable for the transpose below */
data work.want4;
set work.want3;
Batchdate = CAT('Batchdate', NewChange);
run;
/* Transpose to get the batch variables in wide format */
proc transpose
data=work.want4
out=work.want5(drop=_name_ _label_);
by ID;
id Batchdate;
var Date;
run;
/* Add the batch dates to the original data set */
proc sql;
create table work.have_All as
select t1.ID, t1.Visit, t1.Date, t1.Binday, t1.Change,
t2.*
from work.want1 t1
left join work.want5 t2 on (t1.ID = t2.ID);
quit;
/* Get all the variable names in the dataset */
proc sql /* noprint */;
create table work.varnames as
select Name into : vars separated by ' '
from dictionary.columns
where libname = upcase('work')
and memname = upcase('have_All');
quit;
/* Isolate the Batch variable names */
proc sql;
create table work.bnames as
select distinct t1.Name from work.varnames t1
where Name like 'Batch%';
quit;
/* Put those names into a str to be used in the macro below */
proc sql noprint;
select Name into :string separated by " "
from work.bnames;
%let bname = %str(&string);
quit;
%put &bname;
/* Trying to create a macro...but failing */
%macro test();
%let mlength=%sysfunc(countw(&bname)); /*%put &mlength;*/
data work.testresults(drop=i);
set work.have_All;
array restv[1] Visit;
array changev[2] ID Change;
array vdates[1] Date;
array bdates[&mlength] &bname;
array Batch[&mlength] $22;
do i=1 to dim(Batch);
if vdates[1] <= bdates[i] then Batch[i] = 'Yes'; /* Would prefer Batch[i] = 'Batch[i]' but SAS says No */
/* I've tried a couple of fun things here but they didn't work */
if bdates[i] = '.' then Batch[i] = '';
end;
run;
/* Change the date format back for the individual variables...but this needs work */
/* data work.results;*/
/* set work.testresults;*/
/* do i=1 to &mlength;*/
/* bname_new[i] = put(&bname, date11.);*/
/* run;*/
/*end;*/
%mend;
%test;
/*Rename and format some variables - this should be part of the macro (or be a new macro?) so it's also automated */
data work.results_int(drop=Date Change Batchdate1a Batchdate2a Batchdate3a Batchdate4a);
set work.testresults(rename=(Batchdate1=Batchdate1a Batchdate2=Batchdate2a Batchdate3=Batchdate3a Batchdate4=Batchdate4a));
VisitDate = put(Date, date11.);
Batchdate1 = put(Batchdate1a, date11.);
Batchdate2 = put(Batchdate2a, date11.);
Batchdate3 = put(Batchdate3a, date11.);
Batchdate4 = put(Batchdate4a, date11.);
run;
proc sort data=results_int sortseq=linguistic(NUMERIC_COLLATION=on);
by ID Visit;
run;
/* Mark visits whether they're part of a batch or not ... which for some reason doesn't work?! Plus, the results aren't ready yet so this variable wouldn't be correct currently. */
data work.results;
set work.results_int;
if Batchdate1 ne '' then 'Batch?'n = 'Yes'; else 'Batch?'n = 'No';
run;
... View more
