topic Re: How to concatenate character values across variables in SAS Programming

How to concatenate character values across variables

bncoxuk — Wed, 21 Mar 2012 15:52:12 GMT

Hi,

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

sex age married

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_M

age_20

age_25

age_30

married_C

married_S

married_X

How to concatenate character values across variables

Haikuo — Wed, 21 Mar 2012 16:36:45 GMT

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

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

. 20

. 25

. 30

. . C

. . S

. . 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

How to concatenate character values across variables

art297 — Wed, 21 Mar 2012 18:02:32 GMT

The following keeps the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

. 20

. 25

. 30

. . C

. . S

. . 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;

Re: How to concatenate character values across variables

rtritz — Wed, 21 Mar 2012 21:49:19 GMT

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

data have ;

infile cards missover;

input sex :$ age married :$;

cat+1;

cards;

. 20

. 25

. 30

. . C

. . S

. . 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

Re: How to concatenate character values across variables

Tom — Wed, 21 Mar 2012 22:31:38 GMT

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;

How to concatenate character values across variables

bncoxuk — Thu, 22 Mar 2012 14:05:55 GMT

Cool approach.

Re: How to concatenate character values across variables

bncoxuk — Thu, 22 Mar 2012 14:06:19 GMT

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