Concatenate non missing values

Discussion:

(too old to reply)

2007-10-30 18:50:58 UTC

Hi,

Can anyone help me with this.

I am trying to concatenate 6 different variables seperated by a comma in
between them. The only thing I want is that the non missing values of the
variables should be concatenated.
I have already tried the concatenation operator to combine them but does
not seem to work. It concatenates the missing values also, which I do not
want to show.

I tried something like this:

NEW_VAR=VAR1||','||VAR2||','||VAR3||','||VAR4||','||VAR5||','||VAR6;
and gives output like:
NEW_VAR= , ,XX, , ,YY;

Consider example:

GIVEN DATASET:

IDNO VAR1 VAR2 VAR3 VAR4 VAR5 VAR6

1 AA BB CC DD EE
2 AB AC AD AE
3 BC BD BE BF
4 MM MA MB MC MD ME
5 DD DM

OUTPUT WANTED:

IDNO VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 NEW_VAR

1 AA BB CC DD EE AA,BB,CC,DD
2 AB AC AD AE AB,AC,AD,AE
3 BC BD BE BF BC,BD,BE,BF
4 MM MA MB MC MD ME MM,MA,MB,MC,MD,ME
5 DD DM DD,DM

HELP GREATLY APPRECIATED.

THANKS.
DP

data _null_,

2007-10-30 19:06:53 UTC

Permalink

Are you using Version 9. If so the CATX function will handle this easily.

data work.test;
infile cards missover;
array _v[*] $2 var1-var6;
input IDNO _v[*];
newVar = catx(',',of _v[*]);
cards;
1 AA BB CC . DD EE
2 AB . AC AD AE
3 . BC BD BE . BF
4 MM MA MB MC MD ME
5 . DD . DM
;;;;
run;
proc print;
run;

If you are using Version 8 you will need an array and examine each
element for missing.

Post by DP
Hi,
Can anyone help me with this.
I am trying to concatenate 6 different variables seperated by a comma in
between them. The only thing I want is that the non missing values of the
variables should be concatenated.
I have already tried the concatenation operator to combine them but does
not seem to work. It concatenates the missing values also, which I do not
want to show.
NEW_VAR=VAR1||','||VAR2||','||VAR3||','||VAR4||','||VAR5||','||VAR6;
NEW_VAR= , ,XX, , ,YY;
IDNO VAR1 VAR2 VAR3 VAR4 VAR5 VAR6
1 AA BB CC DD EE
2 AB AC AD AE
3 BC BD BE BF
4 MM MA MB MC MD ME
5 DD DM
IDNO VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 NEW_VAR
1 AA BB CC DD EE AA,BB,CC,DD
2 AB AC AD AE AB,AC,AD,AE
3 BC BD BE BF BC,BD,BE,BF
4 MM MA MB MC MD ME MM,MA,MB,MC,MD,ME
5 DD DM DD,DM
HELP GREATLY APPRECIATED.
THANKS.
DP

Jake Bee

2007-10-30 19:10:11 UTC

Permalink

Something like these:

data
a;

length ci $3 new_var
$18;
array var[*] $3
var1-var6;

do i=1 to
6;

if i=3 then ci='
';
else
ci=put(i,3.);

var{i}=ci;

end;

concatvar=compbl(cat(var1,',',var2,',',var3,',',var4,',',var5,',',var6));

NEW_VAR=compbl(VAR1||','||VAR2||','||VAR3||','||VAR4||','||VAR5||','||VAR6);

run;

proc print
data=a;

run;

proc sql
feedback;

select compbl(VAR1||','||VAR2||','||VAR3||','||VAR4||','||VAR5||','||VAR6)
as new_var
into :
new_var

from
a;

quit;

%put my new var:
&new_var;

Jim Groeneveld

2007-10-30 22:29:10 UTC

Permalink

Hi Ad,

A simple example:

DATA Test;
a = 'aa';
b = '';
c = 'cc';
d = '';
e = '';
f = 'ff';
RUN;

DATA Conc (KEEP=Conc);
SET Test;
LENGTH Conc $32; * Sufficient length for all cases;
Conc = TRIM(a) || ',' || TRIM(b) || ',' || TRIM(c) || ',' ||
TRIM(d) || ',' || TRIM(e) || ',' || TRIM(f);
* Conc = TRANWRD (Conc, ' ,', ''); * replaces with single spaces! ;
Conc = TRANWRD (Conc, ' ,', '~'); * unique character;
Conc = COMPRESS (Conc, '~'); * remove unique character;
RUN;

PROC PRINT DATA=Conc; RUN;

The commented-out statement thus can not be used or should be followed by a
COMPRESS statement to remove spaces, but then embedded spaces in the values
would be lost as well.

Always use a LENGTH statement for the result values. Otherwise subsequent
results may be longer that fit in the length determined from the first
assignment (iteration).

Always use the TRIM function to eliminate trailing spaces in character
variables that are not completely filled to their lengths.

This works for both SAS8 and SAS9.

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld

data _null_,

2007-10-30 23:09:36 UTC

Permalink

Your "solution" requires that you refer to each variable A B C D E
with the concatenation which seems too limited. Using an array would
be more logical and allow the program to be somewhat dynamic if need
be.

data work.test;
infile cards missover;
array _v[*] $2 var1-var6;
length newVar $50;
input IDNO _v[*];
do _n_ = 1 to dim(_v);
if _v[_n_] ne ' ' then newVar = trim(newvar)||','||_v[_n_];
end;
newVar=substr(newvar,indexC(newvar,',')+1);
cards;
1 AA BB CC . DD EE
2 AB . AC AD AE
3 . BC BD BE . BF
4 MM MA MB MC MD ME
5 . DD . DM
;;;;
run;
proc print;
run;

Post by Jim Groeneveld
Hi Ad,
DATA Test;
a = 'aa';
b = '';
c = 'cc';
d = '';
e = '';
f = 'ff';
RUN;
DATA Conc (KEEP=Conc);
SET Test;
LENGTH Conc $32; * Sufficient length for all cases;
Conc = TRIM(a) || ',' || TRIM(b) || ',' || TRIM(c) || ',' ||
TRIM(d) || ',' || TRIM(e) || ',' || TRIM(f);
* Conc = TRANWRD (Conc, ' ,', ''); * replaces with single spaces! ;
Conc = TRANWRD (Conc, ' ,', '~'); * unique character;
Conc = COMPRESS (Conc, '~'); * remove unique character;
RUN;
PROC PRINT DATA=Conc; RUN;
The commented-out statement thus can not be used or should be followed by a
COMPRESS statement to remove spaces, but then embedded spaces in the values
would be lost as well.
Always use a LENGTH statement for the result values. Otherwise subsequent
results may be longer that fit in the length determined from the first
assignment (iteration).
Always use the TRIM function to eliminate trailing spaces in character
variables that are not completely filled to their lengths.
This works for both SAS8 and SAS9.
Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld