insertion of corrected variable values

[marieK]

hi guys,

i have a problem:i have a table Beispiel1:

No....Id......VariableName...newValue
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

where i have corrected values for another table Beispiel2:

ID.....Var1..Var2..Var3..Var4..Var5..Var...Var7
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

know i would like to program a macro which automatically set the new value into the variable in Beispiel2 which is written in the variable 'variableName' in Beispiel1.

Do you have any idea how i can programm this or how i can call a column when the name variables with every row?
i thought about something like this but i think its not possible that way.?!:

Data central;
merge Beispiel1(in=a) Beispiel2 (in=b);
by ID;
Run;

Data test;
Set central;
array arr{*} $ n1-n10;
DO i=1 to 10;
If No= i Then arr{i}= VariablenName;
Set (here i need the content of:) arr{i}=NewValue
Where No=i;
End;

Run;

thank you very much for your suggestions!

Marie Message was edited by: marieK

[Patrick]

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;

[marieK]

hi Patrick,

thanks for your very fast answer. if i run the programm i get an error message:

'ERROR: Utility file open failed.
NOTE: Table WORK.CORRECTEDVALS created, with 0 rows and 8 columns.

NOTE: View WORK.NEWVALSTRANSPOSEDMAPPED.VIEW used (Total process time):
real time 1.01 seconds
cpu time 0.07 second
'
my problem is, that i have a program with 200 variables. i am looking for a code where i don´t have to write that:

oalesce(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

for 200 variables.... is there any other possibility??

[Patrick]

Hi Marie
The code runs without error on my machine (sas9.1.3 under windows).
Your 200 vars: Working on something more versatile - it will mainly be querying the dictionary to retrieve the list of variables.
Regards, Patrick

[Patrick]

Hi Marie
That should give you an idea how to do it.
The error you mentioned must be based on something else in your code.
If you have an EG session then best re-connect to the server before you run the code to make sure that you test in a "clean" session.
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 noprint;
/* build coalesce statements */
select cats('coalesce (T.',name,',I.',name)||') as '||strip(name) into :SelectList separated by ','
from dictionary.columns
where libname ='WORK' and memname='INITIALVALS' and memtype='DATA' and name ne 'ID';

/* amend values in table */
create table CorrectedVals as
select
I.ID,
&SelectList
from InitialVals as I left join NewValsTransposedMapped as T
on I.ID=T.ID;

/* clean up */
drop table mapping;
drop table NewVals;
drop table NewValsTransposed;
drop view NewValsTransposedMapped;
quit;

[Patrick]

And here another approach. I like it less as it is transposing the "big" dataset twice.

P.S: This and my earlier code examples assume that the DS with the new values does not contain missings, meaning that the correction is never setting an existing value to missing.



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=InitialVals out=InitialValsTransposed(rename=(_name_=VariableName col1=newValue));
by id;
var Var:;
run;

/* join the 2 DS */
proc sql;
create table InitialValsTransposedAmended as
select I.Id,I.VariableName, coalesce(N.newValue,I.newValue)
from InitialValsTransposed as I left join NewVals as N
on I.id=N.id and upcase(strip(I.VariableName))=upcase(strip(N.VariableName));
quit;

/* restore original data structure */
proc transpose data=InitialValsTransposedAmended out=InitialValsAmended(drop=_name_);
id VariableName;
by id;
run; added upcase(strip(...)) to avoid issues with case and blanks

Message was edited by: Patrick

[marieK]

greaaat it works 😉 thank you so much!! (i took the last version) Message was edited by: marieK

[marieK]

hi its me again 😉

everything worked with my example-datasets....
now i have a problem with the real one..

my macro:

*
Initial: the dataset that should be corrected,
idVar: the ID Variable (depends on the initial-dataset)
NewVals: the table with the corrected values
Final: Output-Dataset;

%Macro correct (Initial=, idVar=, NewVals=, Final=);

/* filter (in the NewVals Table are two IDs for different original tables. thats why i have to filter. to get just datalines for the original table where i want to correct the values. ) */

proc SQL;
Create Table correkt_&idVar as
Select * From &NewVals
Where &idVar IS NOT NULL;
Quit;

/* sort */
Proc Sort data=correkt_&idVar;
by &idVar;
Run;

proc Sort data=&Initial;
by &idVar;
Run;

/* Create identical data structures */
proc transpose data=&Initial out=&Initial.Transposed(rename=(_name_=VariableName col1=NewValue));
by &idVar;
run;

/* join the 2 DS */
proc sql;
create table &Initial.TransposedAmended as
select I.&idVar,I.VariableName, coalesce(N.NewValue,I.NewValue)
from &Initial.Transposed as I left join &NewVals as N
on I.&idVar=N.&idVar and upcase(strip(I.VariableName))=upcase(strip(N.VariableName));
quit;

/* restore original data structure */
proc transpose data=&Initial.TransposedAmended out=&Final(drop=_name_);
id VariableName;
by &idVar;
run;

%Mend correct;

i get following error message:

ERROR: The COALESCE function requires its arguments to be of the same data type.


because i have numeric and character values in my original dataset and in the newVals-dataset is everything character... Message was edited by: marieK

[marieK]

also the proc transpose doesnt work with different data-types...

any idea?

[data_null__]

> because i have numeric and character values in my
> original dataset and in the newVals-dataset is
> everything character...

You will need to use the variable type info in "INITIALVALS" to "fix up" the data in "NEWVALS" with a character and numeric version of variable "VALUE".

Then I would use the good old UPDATE statement to apply the transactions.

for example.

[pre]
data InitialVals;
infile datalines dsd dlm=';' truncover;
input ID Var1 Var2:$8. Var3 Var4:$8. 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
;;;;
run;
proc contents order=varnum;
run;

data NewVals(keep=id VariableName cValue nValue);
infile datalines dsd dlm=';' truncover;
input No Id VariableName $ Value:$8.;
select(vtypeX(variableName));
when('C') cValue = value;
when('N') nValue = input(value,f12.);
otherwise;
end;
return;
set initialVals(keep=var:);
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
;;;;
run;

proc transpose data=newVals out=cvals(drop=_name_);
where not missing(cvalue);
by id;
var cvalue;
id variableName;
run;
proc transpose data=newVals out=nvals(drop=_name_);
where not missing(nvalue);
by id;
var nvalue;
id variableName;
run;
data trans;
merge cvals nvals;
by id;
run;

data updated;
update initialVals trans;
by id;
run;
Proc print;
run;
[/pre]

[marieK]

thanks!! i will try it out
but how can i to this part:

data NewVals(keep=id VariableName cValue nValue);
infile datalines dsd dlm=';' truncover;
input No Id VariableName $ Value:$8.;
select(vtypeX(variableName));
when('C') cValue = value;
when('N') nValue = input(value,f12.);
otherwise;
end;
return;
set initialVals(keep=var:);
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
;;;;
run;

when i have a xls-file??? Message was edited by: marieK

[data_null__]

The question if not clear.

You used PROC IMPORT to get " NewVals" from XLS?

If so then use data step similar to

[pre]
data NewVals(keep=id VariableName cValue nValue);
set ;
select(vtypeX(variableName));
when('C') cValue = value;
when('N') nValue = input(value,f12.);
otherwise;
end;
return;
set initialVals(keep=var:);[/pre]

[marieK]

thank u so much! everything works!

just had to compress my value then everything was all right 😉