Macro code works for some studyids but not others

[jomag]

Hello Everyone,

I am relatively new to SAS macro programming. I have two datasets. The first dataset (A) consists of the customer's beginday and enddays. Some customers have more than 300 rows of beginday and endday. The second dataset (B) consists of specific dates(endpoint date) when the customer had some clinical encounter. My goal is to obtain a new dataset (newAB) wherein the code reads the encounters from B that happened within a specific beginday and endday (within dataset A), and calculate the number of encounters. I wrote up a macro for it. However, the macro works for some customers but not others. I'd be grateful if someone can point at what i am missing in my code. I have attached an excerpt of my datasets (A and B) and what it should look like (NewAB). Thanks so much!! 

Here's my sas code and macros:

data A;
set time;
if studyidnew in (1.01,2.01);/*test with few studyids*/
keep studyidnew BeginDay EndDay;
Run;

data B;
set Endpt;
if studyidnew in (1.01,2.01);/*test with few studyids*/
keep studyidnew t0_Year Endpoint_type Endpoint_Date Endpoint_Diag;
Run;

* =============================================== ;
* Flip (transpose) B records into arrays for each patient;
* 500 records per patient is the maximum ;
* =============================================== ;
%Macro FlipB;
* Clear log because it may stop this program from
running if there are too many iterations;

%Global ii Npatients;
* Now get the number of StudyIDs in file A;
%put ii = &ii ;

* Test;
%put "There are %trim(&npatients) unique patients";
* Now flip B records;
Data BB(Drop=i endpoint_date endpoint_diag);
Set B(where= (studyidnew = &studyidnew)) end=last;
array Endpoint_Types{500} $;
array Endpoint_Dates{500};
Retain studyidnew_old;
Retain ith (0);
Retain Endpoint_Types1-Endpoint_Types500;
Retain Endpoint_Dates1-Endpoint_Dates500;
if (_n_ = 1 | studyidnew ne studyidnew_Old)then
Do;
ith = 0;
studyidnew_Old = studyidnew;
Do i=1 to 500;
Endpoint_Types{i} = ' ';
Endpoint_Dates{i} = .;
End;
End;
ith = ith + 1;
* Same study Id: Populate array;
Endpoint_types{ith} = Endpoint_Type;
Endpoint_Dates{ith} = Endpoint_Date;
if last then output;
run;
* Merge and summarize for iith person;
Data AA(Drop=i);
Set A(where=(studyidnew = &studyidnew));
run;
Data AB(Keep=studyidnew BeginDay EndDay HOSP ED DEATH TRANS);
Set AA;
if (_n_ = 1)then
set BB;
* Summarize;
array Endpoint_Types{500} $;
array Endpoint_Dates{500};
Hosp = 0;
ED = 0;
DEATH = 0;
TRANS = 0;
Do i=1 to ith;
EDay = Endpoint_Dates{i};
if ( EDay < Endday & Eday >= BeginDay)
then
Do;
if Endpoint_Types{i} = 'HOSP' then Hosp = Hosp + 1;
if Endpoint_Types{i} = 'ED' then ED = ED + 1;
if Endpoint_Types{i} = 'DEATH' then DEATH = DEATH + 1;
if Endpoint_Types{i} = 'TRANS' then TRANS = TRANS + 1;
* Put i= BeginDay= Endday= EDay= Hosp= Ed=;
End;
Drop Endpoint_Types1-Endpoint_Types500;
Drop Endpoint_Dates1-Endpoint_Dates500;
End;
run;
%Mend FlipB;
* Macro to process each unique studyid in A;
%macro gogo;
%global ii Npatients;
data A;
Set A;
Retain studyidnew_old;
Retain NPatients (0);
if (_n_ = 1 | studyidnew ne studyidnew_Old)then
Do;
Npatients = NPatients + 1;
studyidnew_Old = studyidnew;
End;
Call symput('Npatients', Npatients);
Call symput('studyidnew'||left(Npatients),studyidnew);
Run;
%Do ii=1 %to &NPatients;
%let studyidnew=%Trim(&&studyidnew&ii);
%put Processing studyidnew = &studyidnew;
%FlipB;
Proc append base=NewAB data=AB Force;
run;
%End;
%MEND gogo;

%gogo;
* NewAB will have the counts;
***********************End of Macro********************************;

[PaigeMiller]

---

---

Some of us will not (or cannot) open Microsoft Office documents because they are a security risk. Better for you to provide your data as SAS data step code, following these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---

@jomag wrote:

However, the macro works for some customers but not others.

---

Please give us a clue ... what didn't work? What was wrong with it? What was the exact error message?

Also, please run your code again, using

options symbolgen mlogic mprint;

as the first command in your code, and then look at the LOG and see if the error is obvious. If not, show us the entire LOG (not just the error messages) by clicking on the {i} icon and pasting the LOG into that window.

[jomag]

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>

[PaigeMiller]

I still want you to tell me exactly what happens that is wrong when STUDYID is 2.01.

[Tom]

---

@PaigeMiller wrote:

I still want you to tell me exactly what happens that is wrong when STUDYID is 2.01.

---

Is STUDYID a numeric variable or a character variable? 

If it is character then your WHERE clause is wrong. You need quotes around the constant value.  If it is character make sure it doesn't have leading zeros or invisible characters (tabs, 'A0'x, '00'x, etc.)

If it is numeric is it stored in the source dataset with the full length of 8 bytes or have the lower order bytes of the floating point number been forced to all zero by not being stored?  Try adding ROUND function to WHERE clause.

[ErikLund_Jensen · Accepted solution]

Hi @jomag 

Im sorry to say, but you have choosen a overly complicated approach. SAS Macro code is not intended for processing of observations in a data set, and you can get your wanted result in a much simpler way. Here is one example:

* Note: last id changed from 2,02 to 2,01 to match values in supplied code and other data set;
data A;
informat Studyidnew $4. Beginday 8. Endday 8.;
input Studyidnew Beginday Endday;
cards;
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	262	292
;
run;

data B;
informat Studyidnew $4. endpoint_type $5. endpoint_date 8.;
input Studyidnew endpoint_type endpoint_date;
cards;
1,01	ED	131
1,01	HOSP	132
1,01	HOSP	1338
2,01	TRANS	152
2,01	ED	261
2,01	HOSP	264
;
run;

proc sql;
	create table w1 as
		select 
			a.Studyidnew,
			a.Beginday,
			a.Endday,
			ifn(b.endpoint_type = 'ED',1,0) as ED,
			ifn(b.endpoint_type = 'HOSP',1,0) as HOSP,
			ifn(b.endpoint_type = 'DEATH',1,0) as DEATH,
			ifn(b.endpoint_type = 'TRANS',1,0) as TRANS ,
			b.endpoint_date
		from A left join B
		on 
			a.Studyidnew = b.Studyidnew 
			and a.Beginday <= b.endpoint_date
			and a.Endday >= b.endpoint_date
		order by 
			a.Studyidnew,
			a.Beginday;
quit;

proc sql;
	create table want as
		select distinct 
			Studyidnew,
			Beginday,
			Endday,
			sum(ED) as ED,
			sum(HOSP) as HOSP,
			sum(DEATH) as DEATH,
			sum(TRANS) as TRANS
		from w1
		group by 
			Studyidnew,
			Beginday;
quit;
			

[jomag]

Thank you very much. I am still learning SAS and Proc SQL but will continue to be more thoughtful in how I code next time.

[tsap]

I'm pretty sure that this line in your code that's causing you the problems you're encountering with your output.

if ( EDay < Endday & Eday >= BeginDay) then Do;

This is based on the fact that I'm guessing that your issue with the output on the 2.01 ID is that when its endpoint is on 261, it isn't populating a count of one in ED (based on the data provided).

the fix would be changing the comparison of EDay and Endday to include equals as well (since the endpoint_date falls directly onto an Endday value, otherwise it will not get included in the output count as expected.

Updated Logic:

if ( EDay <= Endday & Eday >= BeginDay) then Do;

Hope this helps.