Did you actually verify your input and output results to confirm the inference?  Possibly there are/were duplicates?  Consider using PROC FREQ to analyze both files to confirm as desk-checking the results, based on some known input.       ... View more

I encourage you to learn the SAS programming language, as much through help from this list, but also with your own desk-checking and DOC-review. You don't need LAG and if you were to add a PUTLOG _ALL_ ;  statement at various points in your DATA step coding logic, you would learn/understand why not. Your SAS code that assigns PROD_NUMBER based on FIRST.<break_varname> tests is sufficient -- meaning no LAG required.  And you do not need to test each BY variable -- only the "significant" variable in the list that would influence your DO/END or otherwise some SAS assignment logic -- in your case that is the PROD_NUMBER assignment, either as a first-occurrence value of 1 or to increment. Please take the opportunity and review the available SAS DOC and examples. No doubt you will be given the answer at some point, if you continue to throw out code-pieces not thoroughly explored / tested on our own accord. Also, the location of the assignment statement that uses LAG function has no bearing on the location of the BY statement at all. ... View more

Your most effective/efficient approach is not to use LAG but focus on using / exploiting your BY statement variable list and the accompanying FIRST.<break_varname>  DATA step IF/THEN/ELSE coding technique.  Have a look at the SAS Language Elements for relevant information and examples.  And yes LAG is another example, but it has no relevance to also using BY statement coding technique. ... View more

The presumption is your data does appear to be sorted and you need not use PROD in your BY list -- again, the use of BY and LAG are unrelated. Suggestion would be to read up on the DATA step technique related to the DATA step elements being used. Also, suggest you might add to your DATA step some PUTLOG "I am here" _ALL_;   statements for desk-checking with your DATA step operational execution processing to learn more about the BY statement use and how it is exploited and for what purposes -- that being to interrogate FIRST. and LAST.  "break variable" conditions (those variables on the BY statement). ... View more

LAG should not be necessary, given that FIRST.<break_varname> will detect the condition explained as:  "current value equal to prior observation's value" based on the BY variable list shown. ... View more

BY statement processing and LAG function are unrelated - see using the FIRST.<break_variable> instead. Also, from your data example, the ADDRESS-related attribute is influencing PROD_NUM data-value counting, correct? Scott Barry SBBWorks, Inc. ... View more

Recommend you pair down your DATA step logic, perhaps using a stable instream DATALINES; data sample - not your input.  And make use of DATA step PUTLOG _ALL_; to observe SAS behavior when in very controlled process.  This will provide you some insight as to a possible technique / variation, and then implement the simple technique to adapt to your desired input / output data scenario. It will be to your benefit to leverage the SAS processing behavior experience with various DIAGNOSTIC techniques, such as OPTIONS MGEN SGEN MLOGIC;   and PUTLOG as suggested with your own SAS code-piece -- as much (or more for experience gaining) as it would be to pose questions / wait / reply / then iterate.... ... View more

From your example and as was conveyed, you cannot mix macro language within DATA step iteration, so you will need to increment a second SAS DATA step variable (maybe call it n, used as in N+1;) and then make use of that variable when you assign VISIT although using a PUT(N,3.) combined with your other sub-character value assignments. Scott Barry SBBWorks, Inc. ... View more

Okay,so you need a RETAIN SAS variable to use as a base-increment for each observation you iterate. Suggest some self-initiated desk-checking using PUTLOG ">LBL-nnn" / _ALL_;  so that you can decipher DATA step iterations along the path of your program -- the "nnn" you would identify so that you can identify "I am here"..... And so check your DO statement syntax, for example:    DO  I=9  TO  3    BY -1  UNTIL(<condition); Scott Barry SBBWorks, Inc. ... View more

You are mixing SAS DATA step processing MACRO language (%LET stmt) which is resolved typically at DATA / PROC step compilation.  Suggest looking at using PROC FORMAT to assign/derive VISIT based on pre-defined  value ranges. Also, consider opportunity to start out your post by explaining what it is you intend/desire to accomplish in most basic terms -- as well show an abbreviated data-sample (both INPUT side and OUTPUT). Scott Barry SBBWorks, Inc. ... View more