SAS Support Communities

@PetePatel wrote: Thanks PaigeMiller. The variables need to be dropped within the set statement as they are causing issues when trying to set together. Your second macro code is what I was after. If the particular issue(s) were mismatched variable types or lengths of character variables it may be time to address how the data sets are created.   If the issue was just data set size that's another story. ... View more

data have; input ID Month :monyy. _1p_flag; format Month monyy.; cards; 1 Mar-12 0 1 Apr-12 0 1 May-12 1 1 Jun-12 1 1 Jul-12 0 1 Aug-12 0 1 Sep-12 1 1 Oct-12 0 2 Apr-12 1 2 May-12 0 2 Jun-12 0 2 Jul-12 1 2 Aug-12 0 2 Sep-12 0 2 Oct-12 0 2 Nov-12 1 2 Dec-12 0 2 Jan-13 1 2 Feb-13 1 ; data want; retain has_one n 0; do i=1 by 1 until(last._1p_flag); set have; by id _1p_flag notsorted; if first.id then do;has_one=0;n=0;end; if first._1p_flag and _1p_flag=1 and has_one=1 then do; _1_to_0_to_1=1;months_between=n+1;end; output; call missing(_1_to_0_to_1,months_between); end; n=i; if _1p_flag=1 then has_one=1; drop n i has_one; run; ... View more

This is a good task to wax a little didactic about using the queue-based nature of the lag function:   data want; set have (in=firstpass) have (in=secondpass); by id; if firstpass then sum_one_to_zero + (lag(flag)=1 and flag=0); if first.id then sum_one_to_zero=0; if secondpass; if lag(flag)=1 and flag=0 then one_to_zero=1; if lag(id)^=id then one_to_zero=.; run; The statement: if firstpass then sum_one_to_zero + (lag(flag)=1 and flag=0); compares the current flag to the preceding flag, building a total of transitions from 1 to 0. Because the lag function is in the then clause, it is applied only for firstpass cases - secondpass cases never impact the queue underlying the lag function.     To avoid results from the preceding id contaminating the current id, the sum is reset to zero at the start of each ID. if first.id then sum_one_to_zero=0;   The third use of the lag function is more subtle.  The statement if lag(id)^=id then one_to_zero=.; appear to test whether the record-in-hand is the start of an id.  So why not just use if first.id then one_to_zero=.;  /*Do not use this for secondpass*/ Because this part of the program only deals with second_pass observations, while the first.id condition only exists for firstpass observations.  So you basically have to realize that this part of the program is only processing groups of secondpass observations. ... View more

PS good catch on that zero as a starting value. ... View more

Did you run the code and check WANT table ? Var NLevels Name 19 Sex 2 Age 6 Height 17 Weight 15 ... View more

Thanks Paige! ... View more

Thanks! I tried retain with first.date to cover myself but this works fine. ... View more

Yes sorry should have mentioned SAS EG.   Works perfectly now thanks! ... View more

Thank  you novinosrin, I didn't know you could use a groupformat in such a way. ... View more

Here is one approach to retrieve the last five variables of your dataset: proc contents data=have out=want (keep=name varnum where=(name not in ('Date', 'Time', 'Month'))) noprint; run; data list; if _n_=1 then do; declare hash h (dataset:'want', ordered:'d'); h.definekey('VARNUM'); h.definedata('VARNUM','NAME'); h.definedone(); declare hiter C('h'); end; set want; C.first(); output; do i=1 to 4; C.next(); output; end; stop; run; ... View more

Anytime, glad you found your answer 🙂 ... View more