BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
maheshtalla
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

KachiM
Rhodochrosite | Level 12

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;

    Haikuo
    Onyx | Level 15

    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;

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    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.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

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