BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mathis1
Quartz | Level 8

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.

 

Guillaume.PNG

 

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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:

Capture d’écran 2020-05-07 à 16.22.14.png

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;

 

View solution in original post

5 REPLIES 5
ed_sas_member
Meteorite | Level 14

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:

Capture d’écran 2020-05-07 à 16.22.14.png

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
Quartz | Level 8

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
Meteorite | Level 14

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
Quartz | Level 8

Thank you very much ! 😄

ed_sas_member
Meteorite | Level 14

You're welcome @Mathis1 😊

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 734 views
  • 0 likes
  • 2 in conversation