How to concatenate character values across variables

[bncoxuk]

Hi,

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

sex age married

F 

F 

M 

M 

    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

[Haikuo]

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

[art297 · Accepted solution]

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;

[bncoxuk]

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

[rtritz]

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

[Tom]

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;

[bncoxuk]

Cool approach.