Help using Base SAS procedures

Proc SQL ?

Reply
Occasional Contributor bg
Occasional Contributor
Posts: 14

Proc SQL ?

I have two versions of the same data set..same variable names, Data set A1 has missing values, Data set A2 has missing values filled in. I need to produce a third data set, A3 which has contains only the "filled in" values from data set A2 and missing values in all other spaces. For example I need to produce A3 as follows
Data set A1
X1 X2 X3
1 10 20
. 11 .
3 . 22

Data Set A2
X1 X2 X3
1 10 20
2 11 21
3 12 22

Data Set A3
X1 X2 X3
. , .
2 . 21
. 12 .



Is there a way to do this using Proc SQL or any other ideas? Thanks for any suggestions.

PS I'm using PROC MI to create imputation data sets and I'd like to examine only the values that have been imputed for each variable. In my actual data set I have about 300 variables for which I need to compute original values and only the imputed values.
Valued Guide
Posts: 632

Re: Proc SQL ?

Here is a DATA step that will give you the requested data set. Be sure to examine the diagnostics for MI they can give you good insight into how well the values were imputed.
[pre]Data A1 ;
input X1 X2 X3 ;
datalines;
1 10 20
. 11 .
3 . 22
run;
Data A2;
input X1 X2 X3;
datalines;
1 10 20
2 11 21
3 12 22
run;

data a3(keep=xSmiley Happy;
array yy {9999} _temporary_;
set a2;
array xx {*} x:;
do i = 1 to dim(xx);
yy{i} = xx{i};
end;
set a1;
do i = 1 to dim(xx);
if xx{i}=yy{i} then xx{i}=.;
else xx{i} = yy{i};
end;
run;
proc print data=a3;
run;
[/pre]
This solution makes quite a few assumptions about the accuracy of the problem description. Adjustments to reality can be made using the macro language and other techniques.
Super User
Posts: 9,671

Re: Proc SQL ?

Hi.I also find a method to get that.
And also hope someone can use proc sql to get it.


[pre]
Data A1 ;
input X1 X2 X3 ;
datalines;
1 10 20
. 11 .
3 . 22
run;
Data A2;
input X1 X2 X3;
datalines;
1 10 20
2 11 21
3 12 22
run;

data a1;
set a1;
id+1;*just to success to merge two datasets;
run;
data a2;
set a2;
id+1;*just to success to merge two datasets;
run;
data temp;
set a1;
array x{*} x: ;
do i=1 to dim(x);
if not missing(x{i}) then x{i}=9999;*whatever you want,but not the same with your data value. it is just to flag;
end;
drop i;
run;
data tmp;
update a2 temp;
by id ;
run;
data result;
set tmp(drop=id);
array x{*} x: ;
do i=1 to dim(x);
if x{i}=9999 then call missing(x{i});
end;
drop i;
run;
proc print;
run;


[/pre]



Ksharp Message was edited by: Ksharp
Occasional Contributor bg
Occasional Contributor
Posts: 14

Re: Proc SQL ?

Thanks! This is just what I needed. I'll also look into the diagnostics that you mention for Proc MI. Our data set is quite challenging, but if all works out the results will be very useful.
Ask a Question
Discussion stats
  • 3 replies
  • 160 views
  • 0 likes
  • 3 in conversation