Concatenating all variables in an observation in SAS

Question

Is there a general purpose way of concatenating each variable in an observation into one larger variable whilst preserving the format of numeric/currency fields in terms of how it looks when you do a proc print on the dataset. (see sashelp.shoes for example)

Here is some code you can run, as you can see when looking at the log, using the catx function to produce a comma separated output removes both the $ currency sign as well as the period from the numeric variables

proc print data=sashelp.shoes (obs=10);

run;
proc sql;
 select name into :varstr2 separated by ','
 from dictionary.columns
 where libname = "SASHELP" and
 memname = "SHOES";

 quit;


data stuff();

  format all $5000.;

  set sashelp.shoes ;

  all = catx(',',&varstr2.) ;

  put all;
  run;

Any solution needs to be general purpose as it will run on disparate datasets with differently formatted variables.

Answer 1

You can manually loop over PDV variables of the data set, concatenating each formatted value retrieved with vvaluex. A hash can be used to track which variables of the data set to process. If you are comma separating values you will probably want to double quote formatted values that contain a comma.

data want;
  set sashelp.cars indsname=_data;

  if _n_ = 1 then do;
    declare hash vars();
    length _varnum 8 _varname $32;
    vars.defineKey('_n_');
    vars.defineData('_varname');
    vars.defineDone();
    _dsid = open(_data);
    do _n_ = 1 to attrn(_dsid,'NVAR');
      rc = vars.add(key:_n_,data:varname(_dsid,_n_));
    end;
    _dsid = close(_dsid);
    call missing (of _:);
  end;

  format weight comma7.;

  length allcat $32000 _vvx $32000;
  do _n_ = 1 to vars.NUM_ITEMS;
    vars.find();
    _vvx = strip(vvaluex(_varname));
    if index(_vvx,",") then _vvx = quote(strip(_vvx));
    if _n_ = 1 
      then allcat = _vvx;
      else allcat = cats(allcat,',',_vvx);
  end;

  drop _:;
run;

Answer 2

You can use import and export to csv file:

filename tem temp;
proc export data=sashelp.SHOES file=tem dbms=csv replace;
run;

data l;
length all $ 200;
infile tem   truncover firstobs=2;
input all  1-200;
run;

P.S. If you need concatenate only char, uou can create array of all CHARACTER columns in dataset, and just iterate thru:

data l;
length all $ 5000;
set sashelp.SHOES;
array ch [*] _CHARACTER_;
do i = 1 to dim(ch);
    all=catx(',',all,ch[i]);
end;
run;

Answer 3

The PUT statement is the easiest way to do that. You don't need to know the variables names as you can use the _all_ variable list.

put (_all_) (+0);

It will honor the formats attached the variables and if you have used DSD option on the FILE statement then the result is a delimited list.

What is the ultimate goal of this exercise? If you want to create a file you can just write the file directly.

data _null_;
   set sashelp.shoes(obs=3);
   file 'myfile.csv' dsd ;
   put (_all_) (+0);
run;

If you really do want to get that string into a dataset variable there is no need to invent some new function. Just take advantage of the PUT statements abilities by creating a file and then reading the lines from the file.

filename junk temp;
data _null_;
   set sashelp.shoes(obs=3);
   file junk dsd ;
   put (_all_) (+0);
run;
data stuff ;
  set sashelp.shoes(obs=3);
  infile junk truncover ;
  input all $5000.;
run;

You can even do it without creating the full text file. Instead just write one line at a time and save the line into a variable using the _FILE_ automatic variable.

filename junk temp;
data stuff;
  set sashelp.shoes(obs=3);
  file junk dsd lrecl=5000 ;
  length all $5000;
  put @1 (_all_) (+0) +(-2) '  ' @;
  all = _file_;
  output;
  all=' ';
  put @1 all $5000. @;
run;

Answer 4

Solution with vvalue and concat function (||):

It is similar with 'solution without catx' (the last one), but it is simplified by vvalue function instead put.

/*edit sashelp.shoes with missing values in Product as test-cases*/
proc sql noprint;
   create table wocatx as
   select * from SASHELP.SHOES; 
   update wocatx
   set Product = '';
quit;


/*Macro variable for concat function (||)*/
proc sql;
 select ('strip(vvalue('|| strip(name) ||'))') into :varstr4 separated by "|| ',' ||"
 from dictionary.columns
 where libname = "WORK" and
 memname = "WOCATX";
 quit;


/*Data step to concat all variables*/
data stuff2;
  format all $5000.;
  set work.wocatx ;
  all = &varstr4. ;
  put all;
run;

Solution with catx:

proc print data=SASHELP.SHOES;

run;


proc sql;
 select ifc(strip(format) is missing,strip(name),ifc(type='num','put('|| strip(name) ||','|| strip(format) ||')','input('|| strip(name) ||','|| strip(format) ||')')) into :varstr2 separated by ','
 from dictionary.columns
 where libname = "SASHELP" and
 memname = "SHOES";
 quit;


data stuff();

  format all $5000.;

  set sashelp.shoes ;

  all = catx(',',&varstr2.) ;

  put all;
run;

If there isn't in dictionary.columns format, then in macro variable varstr2 will just name, if there is format, then when it would call in catx it will convert in format, that you need, for example,if variable is num type then put(Sales,DOLLAR12.), or if it char type then input function . You could add any conditions in select into if you need.

If there is no need of using of input function just change select:

ifc(strip(format) is missing,strip(name),'put('|| strip(name) ||','|| strip(format) ||')')

Solution without catx:

/*edit sashelp.shoes with missing values in Product as test-cases*/
proc sql noprint;
   create table wocatx as
   select * from SASHELP.SHOES; 
   update wocatx
   set Product = '';
quit;

/*Macro variable for catx*/
proc sql;
 select ifc(strip(format) is missing,strip(name),ifc(type='num','put('|| strip(name) ||','|| strip(format) ||')','input('|| strip(name) ||','|| strip(format) ||')')) into :varstr2 separated by ','
 from dictionary.columns
 where libname = "WORK" and
 memname = "WOCATX";
 quit;

/*data step with catx*/
data stuff;
  format all $5000.;
  set work.wocatx ;
  all = catx(',',&varstr2.) ;
  put all;
run;

/*Macro variable for concat function (||)*/
proc sql;
 select ifc(strip(format) is missing,
            'strip(' || strip(name) || ')',
            'strip(put('|| strip(name) ||','|| strip(format) ||'))') into :varstr3 separated by "|| ',' ||"
 from dictionary.columns
 where libname = "WORK" and
 memname = "WOCATX";
 quit;


/*Data step without catx*/
data stuff1;
  format all $5000.;
  set work.wocatx ;
  all = &varstr3. ;
  put all;
run;

Result with catx and missing values:

Result without catx and with missing values: