I have a SAS Dataset say with name "DATA1" this contain variables Date,Time,X,Y,Z
Now it's having situation like below.
Obs Date Time X Y Z
1 01/10 00:01 1 . . ====> Y & Z are missing
2 01/10 00:02 4 . .
3. 01/10 00:01 . 2 . ====> X & Z are missing
4. 01/10 00:01 . . 3 =====> X & Y are missing ..
Now I want to have them combine so that they give me output like below:
OBS DATE TIME X Y Z
1 01/10 00:01 1 2 3
Any thoughts?
Thankyou in advance...
proc stdize data=have reponly out=want(where=(obs=1));
var x y z;
run;
proc sql;
select date,min(time) as time,min(x) as X,min(y) as Y,min(z) as Z from have group by date;
quit;
If you can safely assume that a non-missing X, Y or Z value will appear only once in the data for each combination of Date and Time, then the following code will work:
proc summary nway data=Have;
class Date Time;
var X Y Z;
output out=Want(drop=_:) sum=; /* or min= or max= */
run;
For the sample data displayed, the result would be:
01/10 00:01 1 2 3
01/10 00:02 4 . .
data have; input ( Date Time X Y Z ) ($); cards; 01/10 00:01 1 . . 01/10 00:02 4 . . 01/10 00:01 . 2 . 01/10 00:01 . . 3 ; run; proc sort data=have;by date time;run; data want; update have(obs=0) have; by date time; run;
Xia Keshan
If your omitting time=02 from your desired results wasn't just an oversight, you may need something like the following:
data have;
input ( Date Time X Y Z ) ($);
cards;
01/10 00:01 1 . .
01/10 00:02 4 . .
01/10 00:01 . 2 .
01/10 00:01 . . 3
;
run;
data need;
set have;
obs_no=_n_;
run;
proc sort data=need;
by date descending obs_no;
run;
data want (drop=obs_no);
update need(obs=0) need;
by date;
run;
Thankyou all for your nice suggestions...they all worked good and since I needed observation like 01/10 00:02 4 . . i found Xia Keshan code worked ..
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.