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
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;
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
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;
Always a 5* quick reply and an answer. Thank you!
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
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;
Cool approach.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.