topic Re: "Merge" several variables in SAS Data Management

"Merge" several variables

Mathis1 — Thu, 07 May 2020 14:12:05 GMT

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 🙂

Re: "Merge" several variables

ed_sas_member — Thu, 07 May 2020 14:22:58 GMT

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;

Re: "Merge" several variables

Mathis1 — Thu, 07 May 2020 14:57:01 GMT

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 ?

Re: "Merge" several variables

ed_sas_member — Thu, 07 May 2020 15:03:33 GMT

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,

Re: "Merge" several variables

Mathis1 — Thu, 07 May 2020 15:07:25 GMT

Thank you very much ! 😄

Re: "Merge" several variables

ed_sas_member — Thu, 07 May 2020 15:10:47 GMT

You're welcome @Mathis1 😊