Hi, I am very new to Snowflake. I am trying to create a SAS dataset from a snowflake table using SAS/ACCESS interface to Snowflake. I can successfully connect to Snowflake database and can create a SAS dataset without errors. Problem is that some of the columns are defined as arrays in Snowflake and after bringing data Snowflake databases into SAS environment, SAS reads array objects as a string where each element of original Snowflake array column is quoted and concatenated inside of a square bracket. I would like to read each element of the Snowflake array as a column in SAS. To add to the complexity a Snowflake column array may have different number of elements in each row. This is what I have: ID Snowflake Array Column: HCPCS_CD PROC PRINT showing SAS column as a string: HCPCS_CODE 1 J3490, J3590, AAAXT ["J3490", "J3590", "AAAXT"] 2 A990, B210 ["A990", "B210"] 3 C220, B210, J3490, J3590 ["C220", "B210", "J3490", "J3590"]   I would like SAS dataset to have variable HCPCS_CD into multiple character columns one for each element of the array: ID HCPCS_CD1 HCPCS_CD2 HCPCS_CD3 HCPCS_CD4 1 J3490 J3590 AAAXT   2 A990 B210     3 C220 B210 J3490 J3590   If SAS can read a Snowflake array column directly as a SAS array that would work too. Thanks and appreciate any help. ... View more

Hi have the following data:   data have; input id$ facility$ start end; length id facility $1 start end 4.; informat start end date9.; format start end mmddyy10.; datalines; 1 A 01jan2010 31jan2010 1 A 01mar2010 31mar2010 1 A 01apr2010 30apr2010 1 A 15jun2010 30jun2010 1 B 15jul2010 31aug2010 1 B 01nov2010 31dec2010 1 B 31jan2011 01mar2011 1 B 03mar2011 31aug2011 1 A 01sep2011 31oct2011 1 A 01dec2011 31dec2011 ; run; I would like to be able to combine all rows into a single row within a facility if difference between start date of current row and end date of previous row is =< 31. So the new start date would be the start date of earliest of combined rows and end date would be the end date of last row within a facility.  The data I want looks like:   ID Facility Start End Comment 1 A 01jan2010 30apr2010 Combines rows 1-3 as gap between current start date and previous end date =< 31 days 1 A 15jun2010 30jun2010 New row as gap between current start date and previous end date > 31 days 1 B 15jul2010 31aug2010 New row as facility changes even if gap condition is satisfied 1 B 01nov2010 31aug2011 New row even if same facility as previous one but the gap is > 31 days 1 A 01sep2011 31dec2011 Rows 9-10 combined as they are within same facility and gape between start date of row 10 and end date of row 9 is =< 31 days  Original data has ~400 million records and is sorted by ID and START but not by FACILITY. I would prefer to avoid resorting and merging. Records from one ID should not be retained into a different ID. Any suggestion are highly appreciated. Thanks ... View more