"Merge" several variables

[Mathis1]

Hello,

I would like to merge into one variable, the values of several variables provided that only one variable appears for each observation. 

I will be more clear with the picture below.

I would like to put the values of Modele, Energie and Puissance, in one variable where the values follow each other. For instance :

Modele 1

Modele 2

Modele 3

Puissance 1

Puissance 2

Puissance 3

Energie 1

Energie 2

Energie 3  

Thank you 🙂

[ed_sas_member · Accepted solution]

Hi @Mathis1 

You can use for example the coalesce() function in PROC SQL:

data have;
	infile datalines dsd missover dlm=" ";
	input modele $ puissance $ energie $ freq;
	datalines;
aaa   1
bbb   1
ccc   1
 zzz  1
 eee  1
 rrr  1
  ccc 1
  ccc 1
  ccc 1
 	;
 	
proc sql;
	create table want as
	select coalesce(modele, puissance, energie) as NEWVAR, *
	from have;
quit;

Output:

 

You can also use a datastep:

data want;
	length NEWVAR $ 50;
	set have;
	if not missing(modele) then NEWVAR=modele;
	else if not missing(puissance) then NEWVAR=puissance;
	else if not missing(energie) then NEWVAR=energie;
run;

[Mathis1]

Thank you very much @ed_sas_member it worked perfectly with the proc SQL wich is very handy when you have a lot a variables.

I was wondering how to automate the data step though... I tried : 

%let varlist = MODELE Puissance Energie ;
%let count = %sysfunc(countw(%quote(&varlist.),%str(" ")));

data want;
	length NEWVAR $ 50;
	set Have;
Do i=2 to &count.;
	if not missing(modele) then NEWVAR=modele;
	else if not missing(scan("&varlist",i,",")) then NEWVAR=scan("&varlist",i,",");
end;
run;

But it clearly doen't work ^^ 
Any idea ? 

[ed_sas_member]

Hi @Mathis1 

You can try this -> it is typically a job for an array:

%let varlist = MODELE Puissance Energie ;

data want;
	length NEWVAR $ 50;
	set Have;
	array _a (*) $ &varlist.;
	do i=1 to dim(_a);
		if not missing(_a(i)) then NEWVAR=_a(i);
	end;
	drop i;
run;

Best,

[Mathis1]

Thank you very much ! 😄

[ed_sas_member]

You're welcome @Mathis1 😊