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 🙂
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;
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;
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 ?
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,
Thank you very much ! 😄
You're welcome @Mathis1 😊
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.