SAS: Delete missing items in a macro variable

Question

I have a macro variable called list1, which gives:

%put &list1.;
A,B,C,D,,,G,,I,,

I need to delete those items which are missing i.e empty between the commas. The final output should be like A,B,C,D,G,I. Is it possible? Trim function does not seem to work here. I'm new to SAS so any help will be appreciated here. Thanks.

try to fix it source level i.e. when you create this macro variable it is much easier. Is your final output a macro variable — Kiran, Jun 28 '19 at 05:42
yeah final output is a macro variable. Its difficult to fix the source level since its coming from a series of macros where the arguments are dynamic — S.Paul, Jun 28 '19 at 05:47
Do you really need comma as the delimiter? Why not space instead? Then extra spaces normally wouldn't matter. What are you using the value for? If it is a list of variable then space delimited is a lot easier to use in SAS code. — Tom, Jun 28 '19 at 12:27

score 2 · Accepted Answer · answered Jun 28 '19 at 06:00

2

 %let a= A,B,C,D,,,G,,I,,;


 data c;
 val = tranwrd(trim(compbl(tranwrd("&a",","," "))), " ",",");
 call symput('b',val);
  run;

%put &b;

This result of A,B,C,D,G,I

answered Jun 28 '19 at 06:00

Kiran

3,255
3
14
21

score 1 · Answer 2 · answered Jun 28 '19 at 09:52

Just for fun - pure macro regex version:

 %let a= A,B,C,D,,,G,,I,,;
 %let regex1 = %sysfunc(prxparse(%str(s/,+/,/)));
 %let b=%sysfunc(prxchange(&regex1,-1,%quote(&a)));

 %let regex2 = %sysfunc(prxparse(%str(s/,$//)));
 %let b=%sysfunc(prxchange(&regex2,-1,%quote(&b)));

 %put b= &b;

You can skip the second regex if you never have to deal with trailing commas at the end of your input.

Tom · Answer 3 · 2019-06-28T12:41:53.090

You could convert the commas to spaces and use COMPBL() to collapse the multiple spaces. Then convert the spaces back into commas. In case you have any spaces just swap the comma and the space characters.

%let temp=%sysfunc(translate(%superq(list1),%str( ,),%str(, )));
%let temp=%sysfunc(compbl(%superq(temp)));
%let list2=%sysfunc(translate(%superq(temp),%str( ,),%str(, )));

PS You don't normally want to use comma as the delimiter in macro variables. It makes it difficult to pass the value to other macros or functions since comma is used to separate argument values. Hence all of the macro quoting functions in this code. For variable lists you would normally want a space delimited list where multiple adjacent spaces wouldn't matter. For other types of lists you would normally want another delimiter like | or ^.

SAS: Delete missing items in a macro variable

3 Answers3