Hi everyone,
I am working on a dataset that contains both old and new bundles of variables. I need to create a new list of variables from these two lists and obtain all the unique values for each row.
Here is an example of what I have:
Subject old_var1 old_var2 old_var3 old_var4 ... new_var1 new_var2 new_var3 new_var4 ...
1 A C B . C D . .
2 X Z . . . . . .
3 C D X . X C D .
Here is what I need to create:
Subject combine_var1 combine_var2 combine_var3 combine_var4 ....
1 A C B D
2 X Z . .
3 C D X .
Please help
Thank you !!
Maybe I'm overlooking something. It seems like you just need to rename some variables. Is there more than that to your question?
data have;
input Subject old_var1 $ old_var2 $ old_var3 $ old_var4 $ new_var1 $ new_var2 $ new_var3 $ new_var4 $;
cards;
1 A C B . C D . .
2 X Z . . . . . .
3 C D X . X C D .
;
run;
proc transpose data=have out=long (rename=col1=value);
by subject;
var old_var1--new_var4;
run;
proc sql;
create table long_want as select distinct subject, value from long where not
missing(Value);
quit;
proc transpose data=long_want out=want(drop=_name_) prefix=combine_var;
by subject;
var value;
run;
@QiaoMaggieZhang wrote:
Hi everyone,
I am working on a dataset that contains both old and new bundles of variables. I need to create a new list of variables from these two lists and obtain all the unique values for each row.
Here is an example of what I have:
Subject old_var1 old_var2 old_var3 old_var4 ... new_var1 new_var2 new_var3 new_var4 ...
1 A C B . C D . .
2 X Z . . . . . .
3 C D X . X C D .
Here is what I need to create:
Subject combine_var1 combine_var2 combine_var3 combine_var4 ....
1 A C B D
2 X Z . .
3 C D X .
Please help
Thank you !!
Take advantage of using the IN operator with an array name to simplify the direct approach.
data have;
input (Subject old_var1-old_var4 new_var1-new_var4) ($);
cards;
1 A C B . C D . .
2 X Z . . . . . .
3 C D X . X C D .
;
data want;
set have;
array old old_var: new_var: ;
array combine[8] $8 ;
do i=1 to dim(old);
if not missing(old[i]) then do;
if old[i] not in combine then do;
j=sum(j,1);
combine[j]=old[i];
end;
end;
end;
drop i j;
run;
Result
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.