Thank you, Paige. I don't know what is wrong but the macro code works for studyid 1.01 but does not work for studyid 2.01. I have attached the log and below the log are the datasets. Thanks again!! options symbolgen mlogic mprint;
18 %Macro FlipB;
19 * Clear log because it may stop this program from
20 running if there are too many iterations;
21
22 %Global ii Npatients;
23 * Now get the number of StudyIDs in file A;
24 %put ii = &ii ;
25
26 * Test;
27 %put "There are %trim(&npatients) unique patients";
28 * Now flip B records;
29 Data BB(Drop=i endpoint_date endpoint_diag);
30 Set B(where= (studyidnew = &studyidnew)) end=last;
31 array Endpoint_Types{500} $;
32 array Endpoint_Dates{500};
33 Retain studyidnew_old;
34 Retain ith (0);
35 Retain Endpoint_Types1-Endpoint_Types500;
36 Retain Endpoint_Dates1-Endpoint_Dates500;
37 if (_n_ = 1 | studyidnew ne studyidnew_Old)then
38 Do;
39 ith = 0;
40 studyidnew_Old = studyidnew;
41 Do i=1 to 500;
42 Endpoint_Types{i} = ' ';
43 Endpoint_Dates{i} = .;
44 End;
45 End;
46 ith = ith + 1;
47 * Same study Id: Populate array;
48 Endpoint_types{ith} = Endpoint_Type;
49 Endpoint_Dates{ith} = Endpoint_Date;
50 if last then output;
51 run;
52 * Merge and summarize for iith person;
53 Data AA(Drop=i);
54 Set A(where=(studyidnew = &studyidnew));
55 run;
56 Data AB(Keep=studyidnew BeginDay EndDay HOSP ED DEATH TRANS);
57 Set AA;
58 if (_n_ = 1)then
59 set BB;
60 * Summarize;
61 array Endpoint_Types{500} $;
62 array Endpoint_Dates{500};
63 Hosp = 0;
64 ED = 0;
65 DEATH = 0;
66 TRANS = 0;
67 Do i=1 to ith;
68 EDay = Endpoint_Dates{i};
69 if ( EDay < Endday & Eday >= BeginDay)
70 then
71 Do;
72 if Endpoint_Types{i} = 'HOSP' then Hosp = Hosp + 1;
73 if Endpoint_Types{i} = 'ED' then ED = ED + 1;
74 if Endpoint_Types{i} = 'DEATH' then DEATH = DEATH + 1;
75 if Endpoint_Types{i} = 'TRANS' then TRANS = TRANS + 1;
76 * Put i= BeginDay= Endday= EDay= Hosp= Ed=;
77 End;
78 Drop Endpoint_Types1-Endpoint_Types500;
79 Drop Endpoint_Dates1-Endpoint_Dates500;
80 End;
81 run;
82 %Mend FlipB;
83 * Macro to process each unique studyid in A;
84 %macro gogo;
85 %global ii Npatients;
86 data A;
87 Set A;
88 Retain studyidnew_old;
89 Retain NPatients (0);
90 if (_n_ = 1 | studyidnew ne studyidnew_Old)then
91 Do;
92 Npatients = NPatients + 1;
93 studyidnew_Old = studyidnew;
94 End;
95 Call symput('Npatients', Npatients);
96 Call symput('studyidnew'||left(Npatients),studyidnew);
97 Run;
98 %Do ii=1 %to &NPatients;
99 %let studyidnew=%Trim(&&studyidnew&ii);
100 %put Processing studyidnew = &studyidnew;
101 %FlipB;
102 Proc append base=NewAB data=AB Force;
103 run;
104 %End;
105 %MEND gogo;
106
107 %gogo;
MLOGIC(GOGO): Beginning execution.
MLOGIC(GOGO): %GLOBAL II NPATIENTS
MPRINT(GOGO): data A;
MPRINT(GOGO): Set A;
MPRINT(GOGO): Retain studyidnew_old;
MPRINT(GOGO): Retain NPatients (0);
MPRINT(GOGO): if (_n_ = 1 | studyidnew ne studyidnew_Old)then Do;
MPRINT(GOGO): Npatients = NPatients + 1;
MPRINT(GOGO): studyidnew_Old = studyidnew;
MPRINT(GOGO): End;
MPRINT(GOGO): Call symput('Npatients', Npatients);
MPRINT(GOGO): Call symput('studyidnew'||left(Npatients),studyidnew);
MPRINT(GOGO): Run;
NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
1:211 2:2 2:13
NOTE: There were 47 observations read from the data set WORK.A.
NOTE: The data set WORK.A has 47 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
SYMBOLGEN: Macro variable NPATIENTS resolves to 2
MLOGIC(GOGO): %DO loop beginning; index variable II; start value is 1; stop value is 2; by
value is 1.
MLOGIC(GOGO): %LET (variable name is STUDYIDNEW)
MLOGIC(GOGO): Beginning compilation of TRIM using the autocall file C:\Program
Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas.
MLOGIC(GOGO): Ending compilation of TRIM.
MLOGIC(TRIM): Beginning execution.
MLOGIC(TRIM): This macro was compiled from the autocall file C:\Program
Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable II resolves to 1
SYMBOLGEN: Macro variable STUDYIDNEW1 resolves to 1.01
MLOGIC(TRIM): Parameter VALUE has value 1.01
MLOGIC(TRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to 1.01
MLOGIC(TRIM): %DO loop beginning; index variable I; start value is 4; stop value is 1; by value
is -1.
SYMBOLGEN: Macro variable VALUE resolves to 1.01
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(TRIM): %IF condition %qsubstr(&value,&i,1) ne is TRUE
MLOGIC(TRIM): %GOTO TRIMMED (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(TRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to 1.01
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(TRIM): Ending execution.
MLOGIC(GOGO): %PUT Processing studyidnew = &studyidnew
SYMBOLGEN: Macro variable STUDYIDNEW resolves to 1.01
Processing studyidnew = 1.01
MLOGIC(FLIPB): Beginning execution.
MPRINT(FLIPB): * Clear log because it may stop this program from running if there are too many
iterations;
MLOGIC(FLIPB): %GLOBAL II NPATIENTS
MPRINT(FLIPB): * Now get the number of StudyIDs in file A;
MLOGIC(FLIPB): %PUT ii = &ii
SYMBOLGEN: Macro variable II resolves to 1
ii = 1
MPRINT(FLIPB): * Test;
MLOGIC(FLIPB): %PUT "There are %trim(&npatients) unique patients"
MLOGIC(TRIM): Beginning execution.
MLOGIC(TRIM): This macro was compiled from the autocall file C:\Program
Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas
SYMBOLGEN: Macro variable NPATIENTS resolves to 2
MLOGIC(TRIM): Parameter VALUE has value 2
MLOGIC(TRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to 2
MLOGIC(TRIM): %DO loop beginning; index variable I; start value is 1; stop value is 1; by value
is -1.
SYMBOLGEN: Macro variable VALUE resolves to 2
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(TRIM): %IF condition %qsubstr(&value,&i,1) ne is TRUE
MLOGIC(TRIM): %GOTO TRIMMED (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(TRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to 2
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(TRIM): Ending execution.
"There are 2 unique patients"
MPRINT(FLIPB): * Now flip B records;
MPRINT(FLIPB): Data BB(Drop=i endpoint_date endpoint_diag);
SYMBOLGEN: Macro variable STUDYIDNEW resolves to 1.01
MPRINT(FLIPB): Set B(where= (studyidnew = 1.01)) end=last;
MPRINT(FLIPB): array Endpoint_Types{500} $;
MPRINT(FLIPB): array Endpoint_Dates{500};
MPRINT(FLIPB): Retain studyidnew_old;
MPRINT(FLIPB): Retain ith (0);
MPRINT(FLIPB): Retain Endpoint_Types1-Endpoint_Types500;
MPRINT(FLIPB): Retain Endpoint_Dates1-Endpoint_Dates500;
MPRINT(FLIPB): if (_n_ = 1 | studyidnew ne studyidnew_Old)then Do;
MPRINT(FLIPB): ith = 0;
MPRINT(FLIPB): studyidnew_Old = studyidnew;
MPRINT(FLIPB): Do i=1 to 500;
MPRINT(FLIPB): Endpoint_Types{i} = ' ';
MPRINT(FLIPB): Endpoint_Dates{i} = .;
MPRINT(FLIPB): End;
MPRINT(FLIPB): End;
MPRINT(FLIPB): ith = ith + 1;
MPRINT(FLIPB): * Same study Id: Populate array;
MPRINT(FLIPB): Endpoint_types{ith} = Endpoint_Type;
MPRINT(FLIPB): Endpoint_Dates{ith} = Endpoint_Date;
MPRINT(FLIPB): if last then output;
MPRINT(FLIPB): run;
NOTE: There were 28 observations read from the data set WORK.B.
WHERE studyidnew=1.01;
NOTE: The data set WORK.BB has 1 observations and 1005 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
MPRINT(FLIPB): * Merge and summarize for iith person;
MPRINT(FLIPB): Data AA(Drop=i);
SYMBOLGEN: Macro variable STUDYIDNEW resolves to 1.01
MPRINT(FLIPB): Set A(where=(studyidnew = 1.01));
MPRINT(FLIPB): run;
WARNING: The variable i in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: There were 29 observations read from the data set WORK.A.
WHERE studyidnew=1.01;
NOTE: The data set WORK.AA has 29 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
MPRINT(FLIPB): Data AB(Keep=studyidnew BeginDay EndDay HOSP ED DEATH TRANS);
MPRINT(FLIPB): Set AA;
MPRINT(FLIPB): if (_n_ = 1)then set BB;
MPRINT(FLIPB): * Summarize;
MPRINT(FLIPB): array Endpoint_Types{500} $;
MPRINT(FLIPB): array Endpoint_Dates{500};
MPRINT(FLIPB): Hosp = 0;
MPRINT(FLIPB): ED = 0;
MPRINT(FLIPB): DEATH = 0;
MPRINT(FLIPB): TRANS = 0;
MPRINT(FLIPB): Do i=1 to ith;
MPRINT(FLIPB): EDay = Endpoint_Dates{i};
MPRINT(FLIPB): if ( EDay < Endday & Eday >= BeginDay) then Do;
MPRINT(FLIPB): if Endpoint_Types{i} = 'HOSP' then Hosp = Hosp + 1;
MPRINT(FLIPB): if Endpoint_Types{i} = 'ED' then ED = ED + 1;
MPRINT(FLIPB): if Endpoint_Types{i} = 'DEATH' then DEATH = DEATH + 1;
MPRINT(FLIPB): if Endpoint_Types{i} = 'TRANS' then TRANS = TRANS + 1;
MPRINT(FLIPB): * Put i= BeginDay= Endday= EDay= Hosp= Ed=;
MPRINT(FLIPB): End;
MPRINT(FLIPB): Drop Endpoint_Types1-Endpoint_Types500;
MPRINT(FLIPB): Drop Endpoint_Dates1-Endpoint_Dates500;
MPRINT(FLIPB): End;
MPRINT(FLIPB): run;
NOTE: There were 29 observations read from the data set WORK.AA.
NOTE: There were 1 observations read from the data set WORK.BB.
NOTE: The data set WORK.AB has 29 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
MLOGIC(FLIPB): Ending execution.
MPRINT(GOGO): ;
MPRINT(GOGO): Proc append base=NewAB data=AB Force;
MPRINT(GOGO): run;
NOTE: Appending WORK.AB to WORK.NEWAB.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: There were 29 observations read from the data set WORK.AB.
NOTE: The data set WORK.NEWAB has 29 observations and 7 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MLOGIC(GOGO): %DO loop index variable II is now 2; loop will iterate again.
MLOGIC(GOGO): %LET (variable name is STUDYIDNEW)
MLOGIC(TRIM): Beginning execution.
MLOGIC(TRIM): This macro was compiled from the autocall file C:\Program
Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable II resolves to 2
SYMBOLGEN: Macro variable STUDYIDNEW2 resolves to 2.01
MLOGIC(TRIM): Parameter VALUE has value 2.01
MLOGIC(TRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to 2.01
MLOGIC(TRIM): %DO loop beginning; index variable I; start value is 4; stop value is 1; by value
is -1.
SYMBOLGEN: Macro variable VALUE resolves to 2.01
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(TRIM): %IF condition %qsubstr(&value,&i,1) ne is TRUE
MLOGIC(TRIM): %GOTO TRIMMED (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(TRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to 2.01
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(TRIM): Ending execution.
MLOGIC(GOGO): %PUT Processing studyidnew = &studyidnew
SYMBOLGEN: Macro variable STUDYIDNEW resolves to 2.01
Processing studyidnew = 2.01
MLOGIC(FLIPB): Beginning execution.
MPRINT(FLIPB): * Clear log because it may stop this program from running if there are too many
iterations;
MLOGIC(FLIPB): %GLOBAL II NPATIENTS
MPRINT(FLIPB): * Now get the number of StudyIDs in file A;
MLOGIC(FLIPB): %PUT ii = &ii
SYMBOLGEN: Macro variable II resolves to 2
ii = 2
MPRINT(FLIPB): * Test;
MLOGIC(FLIPB): %PUT "There are %trim(&npatients) unique patients"
MLOGIC(TRIM): Beginning execution.
MLOGIC(TRIM): This macro was compiled from the autocall file C:\Program
Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas
SYMBOLGEN: Macro variable NPATIENTS resolves to 2
MLOGIC(TRIM): Parameter VALUE has value 2
MLOGIC(TRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to 2
MLOGIC(TRIM): %DO loop beginning; index variable I; start value is 1; stop value is 1; by value
is -1.
SYMBOLGEN: Macro variable VALUE resolves to 2
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(TRIM): %IF condition %qsubstr(&value,&i,1) ne is TRUE
MLOGIC(TRIM): %GOTO TRIMMED (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(TRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to 2
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(TRIM): Ending execution.
"There are 2 unique patients"
MPRINT(FLIPB): * Now flip B records;
MPRINT(FLIPB): Data BB(Drop=i endpoint_date endpoint_diag);
SYMBOLGEN: Macro variable STUDYIDNEW resolves to 2.01
MPRINT(FLIPB): Set B(where= (studyidnew = 2.01)) end=last;
MPRINT(FLIPB): array Endpoint_Types{500} $;
MPRINT(FLIPB): array Endpoint_Dates{500};
MPRINT(FLIPB): Retain studyidnew_old;
MPRINT(FLIPB): Retain ith (0);
MPRINT(FLIPB): Retain Endpoint_Types1-Endpoint_Types500;
MPRINT(FLIPB): Retain Endpoint_Dates1-Endpoint_Dates500;
MPRINT(FLIPB): if (_n_ = 1 | studyidnew ne studyidnew_Old)then Do;
MPRINT(FLIPB): ith = 0;
MPRINT(FLIPB): studyidnew_Old = studyidnew;
MPRINT(FLIPB): Do i=1 to 500;
MPRINT(FLIPB): Endpoint_Types{i} = ' ';
MPRINT(FLIPB): Endpoint_Dates{i} = .;
MPRINT(FLIPB): End;
MPRINT(FLIPB): End;
MPRINT(FLIPB): ith = ith + 1;
MPRINT(FLIPB): * Same study Id: Populate array;
MPRINT(FLIPB): Endpoint_types{ith} = Endpoint_Type;
MPRINT(FLIPB): Endpoint_Dates{ith} = Endpoint_Date;
MPRINT(FLIPB): if last then output;
MPRINT(FLIPB): run;
NOTE: There were 0 observations read from the data set WORK.B.
WHERE studyidnew=2.01;
NOTE: The data set WORK.BB has 0 observations and 1005 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(FLIPB): * Merge and summarize for iith person;
MPRINT(FLIPB): Data AA(Drop=i);
SYMBOLGEN: Macro variable STUDYIDNEW resolves to 2.01
MPRINT(FLIPB): Set A(where=(studyidnew = 2.01));
MPRINT(FLIPB): run;
WARNING: The variable i in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: There were 0 observations read from the data set WORK.A.
WHERE studyidnew=2.01;
NOTE: The data set WORK.AA has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
MPRINT(FLIPB): Data AB(Keep=studyidnew BeginDay EndDay HOSP ED DEATH TRANS);
MPRINT(FLIPB): Set AA;
MPRINT(FLIPB): if (_n_ = 1)then set BB;
MPRINT(FLIPB): * Summarize;
MPRINT(FLIPB): array Endpoint_Types{500} $;
MPRINT(FLIPB): array Endpoint_Dates{500};
MPRINT(FLIPB): Hosp = 0;
MPRINT(FLIPB): ED = 0;
MPRINT(FLIPB): DEATH = 0;
MPRINT(FLIPB): TRANS = 0;
MPRINT(FLIPB): Do i=1 to ith;
MPRINT(FLIPB): EDay = Endpoint_Dates{i};
MPRINT(FLIPB): if ( EDay < Endday & Eday >= BeginDay) then Do;
MPRINT(FLIPB): if Endpoint_Types{i} = 'HOSP' then Hosp = Hosp + 1;
MPRINT(FLIPB): if Endpoint_Types{i} = 'ED' then ED = ED + 1;
MPRINT(FLIPB): if Endpoint_Types{i} = 'DEATH' then DEATH = DEATH + 1;
MPRINT(FLIPB): if Endpoint_Types{i} = 'TRANS' then TRANS = TRANS + 1;
MPRINT(FLIPB): * Put i= BeginDay= Endday= EDay= Hosp= Ed=;
MPRINT(FLIPB): End;
MPRINT(FLIPB): Drop Endpoint_Types1-Endpoint_Types500;
MPRINT(FLIPB): Drop Endpoint_Dates1-Endpoint_Dates500;
MPRINT(FLIPB): End;
MPRINT(FLIPB): run;
NOTE: There were 0 observations read from the data set WORK.AA.
NOTE: The data set WORK.AB has 0 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
MLOGIC(FLIPB): Ending execution.
MPRINT(GOGO): ;
MPRINT(GOGO): Proc append base=NewAB data=AB Force;
MPRINT(GOGO): run;
NOTE: Appending WORK.AB to WORK.NEWAB.
NOTE: There were 0 observations read from the data set WORK.AB.
NOTE: 0 observations added.
NOTE: The data set WORK.NEWAB has 29 observations and 7 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
MLOGIC(GOGO): %DO loop index variable II is now 3; loop will not iterate again.
MLOGIC(GOGO): Ending execution.
108 * NewAB will have the counts;
Here's the data you requested: <code></code> data F; input Studyidnew Beginday Endday; datalines; 1.01 0 158 1.01 159 180 1.01 181 1387 1.01 1388 1388 2.01 0 258 2.01 259 261 2.01 261 292 ; run; <code></code> data G; length endpoint_type $ 5; input Studyidnew endpoint_type $ endpoint_date; datalines; 1.01 ED 131 1.01 HOSP 132 1.01 HOSP 1338 1.01 TRANS 152 2.01 ED 261 2.01 HOSP 264 ; run; Here's how I would the new dataset to look like: Studyidnew Beginday Endday ED HOSP TRANS 1.01 0 158 1 1 0 1.01 159 180 0 0 0 1.01 181 1387 0 1 0 1.01 1388 1388 0 0 0 2.01 0 258 0 0 1 2.01 259 261 1 0 0 2.01 262 292 0 1 0 <span class="token datalines"><span class="token punctuation"> </span></span><code></code>
... View more