Hi
I have one requirement where i need to concatanate values based on existance/presence of value in the column.
For example I have 5 columns and data as below
tel_1 tel_2 tel_3 tel_4 tel_5
24 30 40 50 60
55 32 89 90
45 60
If all values are there then i can directly concatanate as below for rec 1
temp_var= t01||tel_1||t02||tel_2||t03|tel_3 etc
but if there are no values in between as per rec 2 & rec3
temp_var=t01||tel_1||t02||tel_3
similarly for rec3
temp_var=t01||tel_02||t02||tel_5
here first hardcode values(t01, t02, t03..) should be in accending order irrespective of column values.
Please suggest.
Thanks in advance
The key is to align 2 different counter correctly. is doing it on the fly, here is another way of doing it by kicking out all of the missings from start:
data have;
input tel_1-tel_5;
datalines;
24 30 40 50 60
55 . 32 89 90
. 45 . . 60
;
run;
data want;
length _cat want $ 100;
set have;
_cat=catx('-', of tel:);
do _i=1 to n(of tel:);
want=cats(want,'t',put(_i,z2.),scan(_cat,_i));
end;
drop _:;
run;
Don't understand the logic in your assignments. Is 't01' a constant? Do you know how to define constants in SAS?
Please do some work on your side, get when are stuck.
Please provide test data and required output, as your logic seems a bit off. If you want to concatenate then there are several methods - catx, cats, you don't need to check if something is missing, e.g. cats(tel_1,tel_2...) will give you a string which contains any data found in those variables, so what is it exactly you don't want present?
Hope this answers you.
data have;
input tel_1 tel_2 tel_3 tel_4 tel_5;
datalines;
24 30 40 50 60
55 . 32 89 90
. 45 . . 60
;
run;
data want;
length p $5 temp_var $30;
set have;
array k
x = 0;
do i = 1 to dim(k);
if not missing(k) then do;
x + 1;
p = catx('t', put(x, z2.), k);
call catt(temp_var, p );
end;
end;
keep temp_var;
run;
The key is to align 2 different counter correctly. is doing it on the fly, here is another way of doing it by kicking out all of the missings from start:
data have;
input tel_1-tel_5;
datalines;
24 30 40 50 60
55 . 32 89 90
. 45 . . 60
;
run;
data want;
length _cat want $ 100;
set have;
_cat=catx('-', of tel:);
do _i=1 to n(of tel:);
want=cats(want,'t',put(_i,z2.),scan(_cat,_i));
end;
drop _:;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.