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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.