Hi, Its a great platform to learn and enhance SQL skillset. Here I'm struggling to convert below SAS code into T-SQL, could you please help me in this - SAS Code: Data A2; Set A1; nwords = CountW(HCC_LIST, ','); i = 1; ind = 'N'; Do i = 1 To nwords; If HCC = '' Then Do; ind = 'Y'; unique_hcc = 'N'; End; Else if Scan(HCC_LIST, i, ',') Trim(HCC) Then Do; ind = 'Y'; unique_hcc = 'N'; End; If i > nwords And ind = 'N' Then Do; unique_hcc = 'Y'; Drop nwords i ind; Output; End; Run; Sample Input Data (A1): ID HCC HCC_LIST 123 35 16,128,35,185 123 16,128,35,185 123 116 16,128,35,185 123 128 16,128,35,185 Sample Output Data (A2): ID HCC HCC_LIST Unique_HCC 123 35 16,128,35,185 N 123 16,128,35,185 N 123 116 16,128,35,185 Y 123 128 16,128,35,185. N
Hi Chiroop. Thank you for your question. Here is the T-SQL code: -- Create a temporary table A2 SELECT *, nwords = LEN(HCC_LIST) - LEN(REPLACE(HCC_LIST, ',', '')) + 1, i = 1, ind = 'N', unique_hcc = 'N' INTO #A2 FROM A1; -- Loop through the records in the temporary table DECLARE @maxI INT, @currentI INT SELECT @maxI = MAX(i) FROM #A2; SET @currentI = 1; WHILE @currentI nwords and ind = 'N' UPDATE #A2 SET unique_hcc = 'Y' WHERE i > nwords AND ind = 'N'; -- Drop unnecessary columns and select the final result set ALTER TABLE #A2 DROP COLUMN nwords, i, ind; SELECT * FROM #A2; -- Clean up the temporary table DROP TABLE #A2; -- Phillip
