DATA Step, Macro, Functions and more

concatanate values based on checks

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

concatanate values based on checks

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


Accepted Solutions
Solution
‎02-02-2015 09:55 AM
Respected Advisor
Posts: 3,124

Re: concatanate values based on checks

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 telSmiley Happy;

     do _i=1 to n(of telSmiley Happy;

           want=cats(want,'t',put(_i,z2.),scan(_cat,_i));

     end;

     drop _:;

run;

View solution in original post


All Replies
Super User
Posts: 5,257

Re: concatanate values based on checks

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.

Data never sleeps
Super User
Super User
Posts: 7,403

Re: concatanate values based on checks

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?

Super Contributor
Posts: 254

Re: concatanate values based on checks

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

  • tel_1 - tel_5;
  •    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;

    Solution
    ‎02-02-2015 09:55 AM
    Respected Advisor
    Posts: 3,124

    Re: concatanate values based on checks

    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 telSmiley Happy;

         do _i=1 to n(of telSmiley Happy;

               want=cats(want,'t',put(_i,z2.),scan(_cat,_i));

         end;

         drop _:;

    run;

    ☑ This topic is SOLVED.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 4 replies
    • 253 views
    • 0 likes
    • 5 in conversation