Concatenate and Parse Data in SAS

1 Like

Each year after the SAS Hackathon, I generate and distribute a follow-up survey to participants. Based off the feedback, we make changes for the next year's hackathon to improve an already awesome event. Through the software we use to distribute the survey, I export the data to an Excel file, import the file to SAS Viya, and create a series of reports.

Concatenate Columns

There are certain instances when I use several columns of data to represent one question. For example for the question of "Which SAS products did you use during the SAS Hackathon?" I have four columns: a premade list, a text-based freeform option, and mobile versions of each, all of which are comma separated lists. I've attached the spreadsheet containing the data. For the report I first need to combine these columns, keeping the comma separated delimiter. I use the following code for the concatenation.

/* Program to concatenate open source fixed and open source freeform columns*/

/* Make CAS connection */
options cashost="sas-cas-server-shared-main-client" casport=5570;
cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");

/* Create SAS library from a caslib */ 
libname hack24 cas caslib="SAS Hackathon (DNFS)";
 
proc datasets lib=hack24;run;

/* Concat product list columns */
data concatproducts24(keep=productsconcat sasProductsList sasProductsList_14_TEXT mobleSASProductList mobleSASProductList_14_TEXT);
  set hack24.SASHACKATHON2024;
  sp = ',';
  productsconcat=catx(sp, of sasProductsList sasProductsList_14_TEXT mobleSASProductList mobleSASProductList_14_TEXT);
run;

/* Print resulting table */
proc print data=concatproducts24;
run;

/* Upload data back to CAS */
proc casutil incaslib="SAS Hackathon (DNFS)" outcaslib="SAS Hackathon (DNFS)";
	droptable casdata="concatproducts24" quiet;
	load data=work.concatproducts24 casout="concatproducts24" promote;
    save casdata="concatproducts24" casout="concatproducts24" replace ;
run;
quit;
cas mySession terminate;

This results in the following table. The last column is the sum of the other four columns.

Parse Columns

Now that we have a table with a column containing the entire list, I need to split up the comma delimited list. I achieve this with the following code.

/* Program to parse the open source used column*/

/* Make CAS connection */
options cashost="sas-cas-server-shared-main-client" casport=5570;
cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");
 
/* Create SAS library from a caslib */ 
libname hack24 cas caslib="SAS Hackathon (DNFS)";
 
proc datasets lib=hack24;run;

proc print data=hack24.concatproducts24;
  var productsconcat;
title 'Original data';
run;

/*Parse column*/   
data parse_products24 (keep=obsno products i num_comma num_products);
  set hack24.concatproducts24;
  num_comma = count(productsconcat,',');
  num_products = num_comma+1;
  where productsconcat is not null;
  do i = 1 to num_products by 1;
     obsno= _n_;
     products = scan(productsconcat,i,',');
     output;
  end;
run;

/* Print resulting table */
proc print data=parse_products24 noobs;
title 'After splitting with SCAN function';
var obsno products i num_comma num_products;
run;

/* Upload data back to CAS */
proc casutil incaslib="SAS Hackathon (DNFS)" outcaslib="SAS Hackathon (DNFS)";
	droptable casdata="parse_products24" quiet;
	load data="parse_products24" casout="parse_products24" promote;
    save casdata="parse_products24" casout="parse_products24" replace ;
run;
quit;
cas mySession terminate;

This result is a parsed table.

Finally, I move to SAS Visual Analytics and use the data from the parsed table and add it to my report.

A big thank you to @Cynthia_sas for the assistance in writing the code.