Need help with do look

hk2013 — Tue, 03 May 2022 23:16:54 GMT

I have code below that produces a dataset where we find when the ID enrolls and disenrolls from a program . the code below is picking up the first two enrollments but is doesnt pick up the any other enrollments. For example ID attached in the excel the rEnroll_date should be 04/01/20222 but its only picking up the first reenrollment in 01/01/2021. also i how do i add DTGStart3 and DTGEND3 where for this ID is would be

DTGSTART1 DTGEND1 DTGSTART2 DTGEND2 DTGSTART3 DTGEND3

Jan-19 Sep-19 Jan-20 Dec-21 Apr-22 Dec-00

*Dec-00 = Dec-4000

Ive trying adding other do loops but they arent working.

data _null_;
    *convert current pricing file indicator into SASdate;
    	pricedate = MDY(substr("&curr_price_mo.",7,2),1,substr("&curr_price_mo.",4,2)+2000);
		call symput('pricedate_yymmdd',put(pricedate,yymmdd6.));

    *# months from Dec 2014 to current price file (need +1 to include Dec 2014 in month count);
 		currmons = intck('month',"01jul2019"d,pricedate) +1;
		call symput('pricemos',currmons);

run;



DATA mem_qip07 (drop= h i j k );
  SET mem_qip06_1;

  format DTGSTART1 DTGEND1 DTGSTART2 DTGEND2 FIRST_MO initial_disenroll2 MMYYS.;

  format temp_disENROLL_DATE temp_RENROLL_DATE temp_nENROLL_DATE 
          nENROLL_DATE rENROLL_DATE disENROLL_DATE 
          first_mo MMDDYY10.;

  length temp_dis_varname temp_renr_varname temp_nenr_varname $8;

    *Create array of all date variables;
  ARRAY MOS(*)  MON19_01-MON19_12 MON20_01-MON20_12  MON21_01-MON21_12 MON22_01-&curr_price_mo.;

 *Step 1: Assigns FINAL enrollment date to disenroll2, equal to current month for current members;
 do i= 1 to dim(MOS);
 if  MOS{i} ne 0 then disenroll2 = vname(MOS{i});

  end;
  drop i;


 *Step 2: Assigns FIRST enrollment date for patient starting 2018 to current;
  initial_enroll_flag=0;

      do j =1 to  dim(MOS); 

        IF initial_enroll_flag=0 THEN DO;

            IF MOS{j} = 1  THEN DO;
              initial_enroll_flag=1;
              enroll1=vname(MOS{j});
              initial_enroll_cntpls=j+1;
			  initial_enroll_cnt=j;

                
            END;

        END;

      END; *<-- ends the "do j=1 to ... " do-loop;


 *Step 3: Assigns FIRST dis-enrollment date for patient starting 2018 to current;
  initial_disenroll_flag=0;

      do j =1 to  dim(MOS)-1; *<-- # months (global macro var) minus 1;

        IF initial_disenroll_flag=0 THEN DO;

            IF MOS{j} = 1 and MOS{j+1} = 0  THEN DO;
              initial_disenroll_flag=1;
              disenroll1=vname(MOS{j});
              initial_disenroll_cntpls=j+1;
			  initial_disenroll_cnt=j;

                
            END;

        END;

      END; *<-- ends the "do j=1 to ... " do-loop;

	  
  *Step 4: find SECOND time patient is enrolled following FIRST dis-enrollment (above);
  initial_2reenroll_flag=0;

  if initial_disenroll_flag=1 then DO j = initial_disenroll_cnt TO dim(MOS)-1 ; 

        IF initial_2reenroll_flag=0 THEN DO;

            IF MOS{j} = 0 and MOS{j+1} = 1  THEN DO;
              initial_2reenroll_flag=1;
              enroll2=vname(MOS{j+1});
              initial_2reenroll_cntpls=j+1;
			  initial_2reenroll_cnt=j;

                
            END;

        END;

      END; *<-- ends the "do j=1 to ... " do-loop;



   *Step 5: find FIRST time patient is not enrolled after being enrolled starting in 2015 to current;
  temp_disENR=0;

    do j =1 to  dim(MOS)-1;
/*      do j =1 to (&pricemos. - 1); */
	  *<-- # months (global macro var) minus 1;

        IF temp_disENR=0 THEN DO;

            IF MOS{j} = 1 and MOS{j+1} = 0  THEN DO;
              temp_disENR=1;
              temp_dis_varname=vname(MOS{j});
              temp_dis_arraynum=j+1;

                
            END;

        END;

      END; *<-- ends the "do j=1 to ... " do-loop;

   *Step 6: find last time patient is enrolled (re-enrolled) after first time disenrolled (from above) starting in 2015 to current; 
  temp_RENR=0;

                      * I = &pricemos. <-- global macro var for # months in array;
                      *go backwards from current month back to month patient disenrolled;
/*      if temp_disENR=1 then DO I = &pricemos. TO (temp_dis_arraynum) BY -1; */
  if temp_disENR=1 then DO j = initial_disenroll_cnt TO dim(MOS)-1 ; 

        IF temp_RENR=0 THEN DO;

  			 IF MOS{j} = 0 and MOS{j+1} = 1  THEN DO;
				temp_RENR=1;
              temp_renr_varname=vname(MOS{j+1});
              *temp_renr_arraynum=I;

            END;

        END;

      END; *<-- ends the "do I=14 to ... " do-loop;

    *Step 7a: find if patient looks like is newly enrolled starting in 2015 to current;
         
  temp_nENR=0;

        *Start with Jan 2015 in array: "do k=2" 
          will check on data from earlier years later in program;          

      DO k = 2 TO (&pricemos. - 1); *<-- # months (global macro var) minus 1;

        IF temp_nENR=0 THEN DO;

            IF MOS{k} = 0 AND MOS{k+1}=1 THEN DO;
              temp_nENR=1;
              temp_nenr_varname=vname(MOS{k+1});
              *temp_nenr_arraynum=k+1;

            END;           

        END;

      END; *<-- ends the "do k=2 to ... " do-loop;

	      *for months after January;
      contin_enr19 = MIN(OF MON19_01-MON19_12);

*enrolled all months of 2020;

	      *for months after January;
      contin_enr20 = MIN(OF MON20_01-MON20_12);

	  *enrolled all months of 2021;

	      *for months after January;
      contin_enr21 = MIN(OF MON21_01-MON21_12);

	  	      *for months after January;
     contin_enr22 = MIN(OF MON22_01-&curr_price_mo.);

*enrolled any month of 2018;
	     	*for months after January;
/*      ever_enr18 = Max(OF MON18_01-MON18_12);*/

*enrolled any month of 2019;
 	   *----------------->; *for January only;
       *ever_enr19 = mon19_01;

 	   *----------------->; *for January only;
       ever_enr19 = Max(OF MON19_01-MON19_12);

*enrolled any month of 2020;
 	   *----------------->; *for January only;
/*      ever_enr20 = mon20_01;*/

 	   *----------------->; *for January only;
       ever_enr20 = Max(OF MON20_01-MON20_12);

*enrolled any month of 2021;
 	   *----------------->; *for January only;
/*      ever_enr21 = mon21_01;*/

	   ever_enr21 = Max(OF MON21_01-MON21_12);

*enrolled any month of 2020;
 	   *----------------->; *for January only;
/*      ever_enr22 = mon22_01;*/
	   ever_enr22 = Max(OF MON22_01-&curr_price_mo.);


  *Step 9: First pricing file the patient appears in;
      array fm(19:22,1:12) 

				  mon19_01-mon19_12
				  mon20_01-mon20_12
				  mon21_01-mon21_12
				  mon22_01-mon22_12

                  ;
        FIRST_MO=.;

        do p=19 to 22;
          do q=1 to 12;
            if FIRST_MO=. then do;
              if fm{p,q}=1 then FIRST_MO=mdy(q,1,2000+p);
            end;
          end;
        end;
        drop p q;
       



  *Step 10: Format enrollment /re-enrollment date variables;
                  DTGSTART1 = MDY(substr(enroll1,7,2),1,substr(enroll1,4,2)+2000);
				  DTGEND1 = MDY(substr(disenroll1,7,2),1,substr(disenroll1,4,2)+2000);
                  DTGSTART2 = MDY(substr(enroll2,7,2),1,substr(enroll2,4,2)+2000);
				  DTGEND2 = MDY(substr(disenroll2,7,2),1,substr(disenroll2,4,2)+2000);
				  temp_disENROLL_DATE = MDY(substr(temp_dis_varname,7,2),1,substr(temp_dis_varname,4,2)+2000);
				  temp_RENROLL_DATE = MDY(substr(temp_renr_varname,7,2),1,substr(temp_renr_varname,4,2)+2000);
				  temp_nENROLL_DATE = MDY(substr(temp_nenr_varname,7,2),1,substr(temp_nenr_varname,4,2)+2000);


        *If look like a new enrollee from Step 3a/3b AND no evidence of past enrollment, then confirmed they are a new enrollee;
      if ( Max(OF ever_enr8-ever_enr14) ne 1) and (temp_nENR eq 1) then do;
        new_enr=1;
        nENROLL_DATE=temp_nENROLL_DATE;
      end;

        *Otherwise if enrolled in a month prior to 2015 then NOT a new enrollee.
           This means they were enrolled *and* disenrolled prior to 2015, but RE-enrolled 2015 to current
           In other words, there final status is re-enrolled, not new enrolled.
           ;

      else if (temp_nENR eq 1) then do;
        
        new_enr=0;
        nENROLL_DATE=.;        

        re_ENR=1;

        rENROLL_DATE = max(temp_renroll_date,temp_nENROLL_DATE);
        
      end;

        *else they were never a possible new enrollee to begin with so set their final new enrollee values to "No";
          *coding as above;
      else if (temp_nENR eq 0) then do;
        new_enr=0;    
        nENROLL_DATE=.;
      end;
  
      
  *Step 11: Final variable clean-up of temp_ vs. perm vars for dis-/re-enroll variables not incuded in Step 4;

          *If not already flagged as a re-enrollment situation, assign according to temp variables;
        if re_enr=. then re_enr=temp_RENR;
        if rENROLL_DATE=. then RENROLL_DATE=temp_RENROLL_DATE;

          *For parallel with re-enroll and new variables;
        dis_enr=temp_disENR;
        disENROLL_DATE=temp_disENROLL_DATE;


		        *Indicator flags for additional QC checks;
      if (nENROLL_DATE ne .) then do;
        if (dtgp gt nENROLL_DATE) then dtgp_after_new=1;
        else if (dtgp eq nENROLL_DATE) then dtgp_same_new=1;
        else if (dtgp lt nENROLL_DATE) then dtgp_before_new=1;
      end;

      if (RENROLL_DATE ne .) and (dtgp gt RENROLL_DATE) then dtgp_after_re=1;
      if (disENROLL_DATE ne .) and (dtgp gt disENROLL_DATE) then dtgp_after_dis=1;
      
 
  *Step 12: Reassign final dates ;
	if DTGEND1 = DTGEND2 then DTGEND2 = .;
	if DTGEND1 = . then DTGEND1 = DTGEND2;
	if DTGEND1 = DTGEND2 then DTGEND2 = .;
	if DTGEND1 = "&curr_month"d then DTGEND1 = '31DEC4000'd;
	if DTGEND2 = "&curr_month"d then DTGEND2 = '31DEC4000'd;



run;

Re: Need help with do look

AMSAS — Thu, 05 May 2022 13:41:39 GMT

@hk2013 - Can you simplify the question to a "have" dataset, a "want" dataset and the rules for any logic

Provide the Have/Want datasets as SAS code e.g.

data have ;
	infile cards ;
	input id $ value ;
cards ;
a 1
a 2
b 1
c 1
c 2
c 3
d 1
d 2
d 3
d 4
;
run ;


data want ;
	infile cards ;
	input id $ value ;
cards ;
a 2
b 1
c 3
d 4
;
run ;

Logic : I want to count the number of occurrences of each ID

Here's a helpful Macro that can turn a SAS dataset into code

Data2DataStep Maco instructions will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

FYI - You probably don't have any responses for several reasons

Attached file - A lot of users will not download/open attachments for security concerns
Relatively long code which will take time to analyze
The problem description is not clear (at least to me)

topic Need help with do look in SAS Enterprise Guide

Need help with do look

Re: Need help with do look