DATA Step, Macro, Functions and more

How to concatenate character values across variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

How to concatenate character values across variables

Hi,

In my SAS file, the table looks like below (show only 3 variables here). The blank space are missing values.

sex age married

    20

    20

    25

    25

    30

    30

            C

            C

            S

            S

            X

            X

Now the task is to add a new column which concatenates all the values across all those character variables, and put the variable name in front of the values. It should be like below.

varname

sex_F

sex_F

sex_M

sex_M

age_20

age_20

age_25

age_25

age_30

age_30

married_C

married_C

married_S

married_S

married_X

married_X


Accepted Solutions
Solution
‎03-21-2012 02:02 PM
PROC Star
Posts: 7,358

How to concatenate character values across variables

The following keeps the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

F

F

M

M

.   20

.   20

.   25

.   25

.   30

.   30

.   .       C

.   .       C

.   .       S

.   .       S

.   .       X

.   .       X

;

proc sql noprint;

  select "if not missing("||name||

         ") then newvar=catx('_','"||

         trim(name)||"',"||trim(name)||")"

    into :names separated by ";"

      from dictionary.columns

        where libname="WORK" and

        memname="HAVE"

  ;

quit;

data want;

  set have;

  &names.;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

How to concatenate character values across variables

Try this, it will get your contents, but not the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

F

F

M

M

.   20

.   20

.   25

.   25

.   30

.   30

.   .       C

.   .       C

.   .       S

.   .       S

.   .       X

.   .       X

;

proc transpose data=have out=have1;

   var _all_;

   copy _all_;

   run;

   proc sort data=have1;

   by _name_;

   run;

proc transpose data=have1(where=(not missing(_name_))) out=have2;

   by _name_;

   var col:;

   run;

   data want (keep=new);

   set have2 (where=(not missing(col1)));

   length new $40.;

   new=strip(_name_)||'_'||strip(col1);

   run;

proc print;run;

Regards,

Haikuo

Solution
‎03-21-2012 02:02 PM
PROC Star
Posts: 7,358

How to concatenate character values across variables

The following keeps the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

F

F

M

M

.   20

.   20

.   25

.   25

.   30

.   30

.   .       C

.   .       C

.   .       S

.   .       S

.   .       X

.   .       X

;

proc sql noprint;

  select "if not missing("||name||

         ") then newvar=catx('_','"||

         trim(name)||"',"||trim(name)||")"

    into :names separated by ";"

      from dictionary.columns

        where libname="WORK" and

        memname="HAVE"

  ;

quit;

data want;

  set have;

  &names.;

run;

Frequent Contributor
Posts: 131

Re: How to concatenate character values across variables

Always a 5* quick reply and an answer. Thank you!

Contributor
Posts: 23

Re: How to concatenate character values across variables

Hello, Slightly modifying the input dataset, another possibility is below:

data have ;

infile cards missover;

input sex :$ age married :$;

cat+1;

cards;

F

F

M

M  

.     20

.     20

.     25

.     25

.     30

.     30  

.     .    C

.     .    C

.     .     S

.     .     S

.     .     X

.     .     X

;

proc transpose data = have out=thave;  

by cat;   var sex age married;

run;

data thave2(drop=cat _name_ col1 col2);  

set thave;  

col2 = strip(col1);  

if col1 = ' ' then delete;  

if col2 ne '.' then do;

varname = catx('_',_name_,col2);

output;  

end;

run;

HTH, Rich

Super User
Super User
Posts: 6,498

Re: How to concatenate character values across variables

Not sure what you want to do when an observation has more than one variable with a non missing value.  This code assumes you want to concatenate those values into the result with a space between them.

You can use VNEXT to loop through the variables in your dataset. You can use vvaluex to get the value based on the variable name.

data have;

  input sex $ age married $ @@;

cards;

F . . F . . M . . M . .

. 20 . .  20 . . 25 . . 25 . . 30 . . 30 .

. . C . . C . . S . . S . . X . . X

run;

data want ;

  set have ;

  length _name_ $32;

  length _value_ $2000 ;

  do while(1);

    call vnext(_name_);

    if upcase(_name_) eq '_NAME_' then leave;

    if strip(vvaluex(_name_)) not in (' ','.') then

      _value_=catx(' ',_value_,catx('_',_name_,vvaluex(_name_)))

    ;

  end;

  keep _value_;

run;

Frequent Contributor
Posts: 131

How to concatenate character values across variables

Cool approach.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 376 views
  • 7 likes
  • 5 in conversation