Hi Marie
Not a macro but it should work.
HTH
Patrick
data NewVals;
infile datalines dsd dlm=';' truncover;
input No Id VariableName $ newValue;
drop No;
datalines4;
1;501;Var1;100
2;512;Var2;101
3;523;Var3;102
4;534;Var4;103
5;545;Var4;104
6;556;Var4;105
7;567;Var5;106
8;578;Var6;107
9;589;Var7;108
10;600;Var7;109
;;;;
data InitialVals;
infile datalines dsd dlm=';' truncover;
input ID Var1 Var2 Var3 Var4 Var5 Var6 Var7;
datalines4;
501;10;20;33;;13;;;
512;11;;32;;75;;;
523;12;21;56;;69;;;
534;13;22;34;46;35;;;
545;14;23;34;;;56;;
556;15;;36;;73;;54
567;16;24;37;;;;;
578;17;25;;;;;;
589;18;26;38;;99;78;;
600;;27;;;55;;54
;;;;
/* Create identical data structures */
proc transpose data=NewVals out=NewValsTransposed;
id VariableName;
by id;
run;
/* make sure all variables exist in DS NewValsTransposed */
data mapping;
stop;
set InitialVals;
run;
data NewValsTransposedMapped /view=NewValsTransposedMapped;
set mapping NewValsTransposed;
run;
/* Amend values */
proc sql;
create table CorrectedVals as
select
I.ID,
coalesce(T.Var1,I.Var1) as Var1,
coalesce(T.Var2,I.Var2) as Var2,
coalesce(T.Var3,I.Var3) as Var3,
coalesce(T.Var4,I.Var4) as Var4,
coalesce(T.Var5,I.Var5) as Var5,
coalesce(T.Var6,I.Var6) as Var6,
coalesce(T.Var7,I.Var7) as Var7
from InitialVals as I left join NewValsTransposedMapped as T
on I.ID=T.ID;
quit;
... View more
