How to read a Snowflake table with array columns into SAS environment using SAS/ACCESS inteface

[dxiao2017]

Hi @arorata , I update the code in my previous thread. Just for your reference. This code can be used when your raw data has more variations (I created two more rows in the raw data to illustrate this ) in the hcpcs_cd column. The code and output are as follows.

/*prepare raw data*/
data have;
   input id hcpcs_cd /*$60.*/ $56.;
   col_length=length(hcpcs_cd);
   retain maxcollngh;
   maxcollngh=max(maxcollngh,col_length);
   datalines;
1 J3490, J3590, AAAXT
2 A990, B210
3 C220, B210, J3490, J3590
4 A567890AB, B210009CDE, C200, , , CDE2300, AABBCC00112210
5 AABBCCDDEE1234567890
;
run;
proc print data=have noobs;
   var id hcpcs_cd;
run;
data _null_;
   set have end=last;
   if last then 
      call symputx('maxcollngh',maxcollngh);
run;
%put &maxcollngh;/*56*/
/*determine how many new columns
the new dataset has*/
proc sql noprint;
select max(countw(hcpcs_cd,','))
   into :num trimmed
   from have;
quit;
%put #/*7*/
/*determine the maximum length
of the new columns*/
data have1;
   set have;
   array len[&num] newcol_len1-newcol_len#
   do i=1 to #
      len[i]=length(scan(hcpcs_cd,i,','));
   end;
   maxlen_row=max(of newcol_len1-newcol_len&num);
   retain maxlen_newcol;
   maxlen_newcol=max(maxlen_newcol,maxlen_row);
run;
data _null_;
   set have1 end=last;
   if last then 
      call symputx('maxlen_newcol',maxlen_newcol);
run;
%put &maxlen_newcol;/*20*/
/*create new dataset*/
data want;
   set have;
   array newcol[&num]$&maxlen_newcol hcpcs_cd1-hcpcs_cd#
   do i=1 to #
      newcol[i]=scan(hcpcs_cd,i,',');
   end;
   keep id hcpcs_cd1-hcpcs_cd7;
run;
proc print data=want noobs;run;