I think a Data step would be a good approach; a few million rows is nothing to a Data step.   Here's some sample code, below, followed by the results.  Are these results what you want?   Jim   DATA Have; INPUT Unique_ID Age Segment; DATALINES; 1 56 0 1 57 4 1 57 0 2 23 0 2 24 0 2 24 1 3 77 0 3 77 2 3 78 0 3 78 0 4 86 0 4 88 1.1 4 88 0 5 12 0 5 12 0 5 14 0 5 14 0 ; RUN; PROC SORT DATA=Have; BY Unique_ID Age DESCENDING Segment; RUN; DATA Want; DROP _:; SET Have; BY Unique_ID Age; RETAIN _Prior_Segment 0; IF Segment > 0 THEN DO; _Prior_Segment = Segment; END; ELSE DO; Segment = _Prior_Segment; END; OUTPUT; IF LAST.Age OR LAST.Unique_ID THEN _Prior_Segment = 0; RUN;     ... View more

It's only going to include rows in the results set where the dates are greater in the second table than the first.   from fake_data a, fake_data b where a.pat_id = b.pat_id and b.visit_date > a.visit_date and intck('day', b.visit_date, a.visit_date) < 366; It's looking for visits after the first visit but within one year of the first visit.  In other words, it's looking to gather the rows of patient who visited more than once in a rolling year period.   By the way, if you'll notice, the join is between two ... well sort of virtual instances of the same table.  This is a real cool feature of SQL that you can join tables to themselves.   Jim ... View more

Ah, well, you didn't say that in your original post, but it can be accommodated.  Take a look at the below code and results.    I'm basically scanning through all the rows and setting Boolean flags for each of the four codes you specified.  When LAST. is true, I evaluate the flags to see what combination of codes has been encountered.  If the combination meets the criteria, a row is added to the Keys dataset which is later inner joined with the Have dataset to obtain our final results.  I've found the Boolean flag technique for situations like this.  It's worth knowing about.   Jim   DATA Have; FORMAT ID $5. Date MMDDYY10. Code $5.; INFILE DATALINES DSD DLM='09'X; INPUT CODE $ ID $ DATE : MMDDYY10.; DATALINES; 64640 12345 06/30/2020 64640 12345 06/30/2020 64640 23456 02/10/2020 C2618 23456 02/10/2020 C2618 34567 06/24/2020 C2618 45678 01/06/2020 0442T 56789 02/11/2020 C2618 67890 02/11/2020 64640 78901 02/14/2020 0441T 89012 02/10/2020 C2618 90123 02/17/2020 C2618 11234 01/22/2020 64640 22345 02/10/2020 64640 22345 02/10/2020 C2618 22345 02/10/2020 64640 22345 02/10/2020 64640 22345 02/10/2020 64640 22345 02/10/2020 0441T 33456 03/16/2020 C2618 33456 03/16/2020 64640 44555 03/22/2020 C2618 44555 04/18/2020 ; RUN; PROC SORT DATA=Have; BY ID DATE; RUN; DATA Keys; DROP _: Code; SET Have; BY ID DATE; RETAIN _CD0441T 0 _CD0442T 0 _CD64640 0 _CDC2618 0 ; SELECT (UPCASE(CODE)); WHEN ('0441T') _CD0441T = 1; WHEN ('0442T') _CD0442T = 1; WHEN ('64640') _CD64640 = 1; WHEN ('C2618') _CDC2618 = 1; OTHERWISE DO; END; END; IF Last.DATE THEN DO; IF (_CD0441T AND NOT (_CD64640 OR _CDC2618)) OR (_CD0442T AND NOT (_CD64640 OR _CDC2618)) OR (_CD64640 AND _CDC2618) THEN DO; CALL MISSING(_CD0441T,_CD0442T,_CD64640,_CDC2618); OUTPUT; END; ELSE DO; CALL MISSING(_CD0441T,_CD0442T,_CD64640,_CDC2618); DELETE; END; END; ELSE DO; DELETE; END; RUN; PROC SQL NOPRINT; CREATE TABLE Want AS SELECT Have.* FROM Have INNER JOIN Keys ON Have.ID = Keys.ID AND Have.Date = Keys.Date ; QUIT; Results:   ... View more

@Ronein,   Well, first, I agree with @andreas_lds and @PaigeMiller that transforming from long to wide is generally a poor idea.  However, maybe it's for reporting or for export as a .csv file, so maybe it's worthwhile.   That said, here's one solution, below.  The Call Missing() is probably unnecessary but is there at least in part to make it clear that the IPs are reset whenever there is a new ID.  Below the code are the results. %let CustomerIP=1234567; Data have; input ID CustomerIP; cards; 111 1234567 222 1234567 333 8788888 444 8798777 555 8765432 222 8765432 222 7654221 111 8273774 ; Run; PROC SORT DATA=Have; BY ID CustomerIP; RUN; DATA Want; DROP CustomerIP; SET Have; BY ID CustomerIP; LENGTH IPs $32767; RETAIN IPs; IF FIRST.ID THEN DO; CALL MISSING(IPs); IPs = STRIP(PUT(CustomerIP,8.)); END; ELSE DO; IPs = CATS(IPs,',',PUT(CustomerIP,8.)); END; IF LAST.ID; IF INDEX(IPs , "&CustomerIP"); RUN; Results:   Jim ... View more

@raj712,   @Quentin's advice is good.  Get the SAS job running properly as just a SAS job first.  I imagine that it's running fine, but it looks like you might need to make a few adjustments.   First, when you see this type of error: ERROR: Insufficient authorization to access C:\Program Files (x86)\Microsoft SQL Server\130\DTS\binn\CallScoring.log. it's usually because no log path was specified or the path was specified incorrectly.   What command string are you using in C# to invoke SAS?  I would like to see the entire command string.  You probably need to put a fully qualified log path in your command string otherwise, SAS will try to write the log to where ever it is invoked from.  If you're invoking SAS from C#, it might therefore be trying to write the log to C:\Program Files (x86)\Microsoft SQL Server\130\DTS\binn.   The second error is occurring because you're executing two instances of CallScoring concurrently.  They're both trying to write to the same log.  You need to create a dynamic log file name.  I usually use "directives" to assign such a dynamic name, but you could do it in your C# code.   ERROR: File is in use, c:\Users\abc\CallScoring.log   Here's how I typically do it in my command line string (this is just the log portion of the string): -log c:\Users\abc\CallScoring_#Y-#m-#d_#H-#M-#s_#p.log "#Y-#m-#d_#H-#M-#s" places the year-month-day-hour-minute-second into the filename of the log file.  #p places the Windows process ID into the file name which guarantees that the name will be unique and should eliminate the "File is in use" error.   Jim ... View more

For something like this, you'll need to put some kind of flag or indicator in the data which can be used to determine whether inbound or outbound will apply.   I would adjust your code as follows: data retentions; input QA_score user : $20. Band_Indicator $; datalines; 114 ALUPIYA I 130 DLOUIS I 150 DMORAKE I 100 FDANISA O 122 FFARAO O ; RUN; proc format; invalue inbound_save_band low -< 114 = 0 114 -< 137 = 0.08 137 -< 152 = 0.16 152 -< 167 = 0.24 167 -< 190 = 0.32 190 - high = 0.4 ; run; proc format; invalue outbound_save_band low -< 82 = 0 82 -< 98 = 0.08 98 -< 109 = 0.16 109 -< 120 = 0.24 120 -< 136 = 0.32 136 - high = 0.4 ; run; DATA Want; SET Retentions; IF UPCASE(Band_Indicator) = 'I' THEN Inbound_Outbound_Score = INPUT(PUT(QA_Score, 3.), Inbound_Save_Band3.); ELSE IF UPCASE(Band_Indicator) = 'O' THEN Inbound_Outbound_Score = INPUT(PUT(QA_Score, 3.), Outbound_Save_Band3.); ELSE CALL MISSING(Inbound_Outbound_Score); RUN; Results:     Jim ... View more