topic Re: Dynamic concatenation in dataset in SAS Programming

Dynamic concatenation in dataset

keen_sas — Thu, 27 Jun 2019 13:06:16 GMT

There are few variables along with some text provided in spreadsheet or in another dataset. The task is to concatenate the variables along with the text in that particular dataset. Before calling the specified condition into the required dataset to concatenate , it has to be converted into proper SAS syntax/algorthim to make it work as mentioned below. I am using sashelp.svrtdist to test this.

S.No	Original (Have)	SAS Algorthim to make it work (WANT)
1	type " (" subtype name ")"	catx('',type, " (", subtype, name, ")" );
2	(' type sequence name ')'	catx('',' (', type, sequence, name, ')') ;
3	type " combination of type and subtype (" subtype name ")"	catx('',type, " combination of type and subtype (", subtype, name ")");
4	type " (" subtype ")" ' - ' name	catx('',type, " (",subtype,")", '-', name);

%macro test ;
data text ;
length have $50.;
have='type " (" subtype name ")"';output;
have="'(' type sequence name ')'";output;
have="type 'combination of type and subtype (' name ')' ";output ;
have='type " - " subtype name' ;output ;
run ;

data text ;
set text ;
if not missing(have) then want="catx('',"||tranwrd(strip(have),' ' ,',') ||');' ;
call symputx('cnt',_n_) ;
call symputx (cats("cond",_n_),want) ;
run ;

data test;
set sashelp.svrtdist (keep=type subtype name sequence owner);
%do i =1 %to &cnt;
want&i=&&cond&i ;
%end;
run ;

%mend test ;

%test ;

Type, subtype, name are the variables present in SASHELP.SVRTDIST dataset and the condition what to concatenate are placed in TEXT dataset with HAVE as variable . To concatenate these variables i have to convert them into SAS acceptable syntax as present in another column WANT as shown above.

1) The issue with this is " (" and ' - ' since there is a space between quote and ", this space is also converting into comma, which should not be done. The string present between the quotes is having spaces between the words, these sapces also converted to commas.

2) After concatenation there should not be any space between the parenthesis like Header ( Package ), it should be like Header(Package).

is there any easier method/alternative approach to make this concatenation work in the dataset in dynamic way without any manual intervention.

Re: Dynamic concatenation in dataset

karan124 — Fri, 28 Jun 2019 00:33:02 GMT

Hello, if it's about dealing with the blanks, can't you use COMPBL (Compress Blanks) function?

Re: Dynamic concatenation in dataset

Tom — Fri, 28 Jun 2019 03:47:57 GMT

Two questions.

1) Why are you using CATX() instead of CATS() if you don't actually include anything to insert between the values?

2) Why are you storing the strings in the dataset with spaces as the delimiter if you need to use them with commas as the delimiter?

The easiest way to convert the delimiters in a list is to parse the list and re-create it.

data text;
  input id have $50.;
cards;
1	type " (" subtype name ")"	
2	'(' type  sequence  name ')'
3	type " combination of type and subtype (" subtype name ")"	
4	type " (" subtype ")"  ' - ' name
;

data fix ;
  set text;
  length code $200 ;
  do i=1 to countw(have,' ','q');
     code=catx(',',code,scan(have,i,' ','q'));
  end;
  code=cats("catx(' ',",code,')');
  drop i;
run;

Obs    id    have

 1      1    type " (" subtype name ")"
 2      2    '(' type  sequence  name ')'
 3      3    type " combination of type and subtype (" subtype
 4      4    type " (" subtype ")"  ' - ' name

Obs    code

 1     catx(' ',type," (",subtype,name,")")
 2     catx(' ','(',type,sequence,name,')')
 3     catx(' ',type," combination of type and subtype (",subtype)
 4     catx(' ',type," (",subtype,")",' - ',name)

Re: Dynamic concatenation in dataset

keen_sas — Fri, 28 Jun 2019 06:36:58 GMT

Thank you Tom for your quick response. For your questions 1) In case if any delimiter is specified , we have to use CATx instead of CATs, so to avoid this change using CATx.
2) Its the specification provided by the team ,its mandatory to use, cannot avoid it.