BookmarkSubscribeRSS Feed
rkumar23
Calcite | Level 5

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...

6 REPLIES 6
stat_sas
Ammonite | Level 13

proc stdize data=have reponly out=want(where=(obs=1));

var x y z;

run;

slchen
Lapis Lazuli | Level 10

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;

mark_alexander_ct_gov
Fluorite | Level 6

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  .  .

Ksharp
Super User
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

art297
Opal | Level 21

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;

rkumar23
Calcite | Level 5

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 ..


sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 970 views
  • 5 likes
  • 6 in conversation