Help using Base SAS procedures

SAS Dataset with Variables Merge

Reply
Frequent Contributor
Posts: 78

SAS Dataset with Variables Merge

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

Trusted Advisor
Posts: 1,204

Re: SAS Dataset with Variables Merge

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

var x y z;

run;

Super Contributor
Posts: 275

Re: SAS Dataset with Variables Merge

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;

New Contributor
Posts: 4

Re: SAS Dataset with Variables Merge

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=_Smiley Happy 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  .  .

Super User
Posts: 9,691

Re: SAS Dataset with Variables Merge

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

PROC Star
Posts: 7,366

Re: SAS Dataset with Variables Merge

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;

Frequent Contributor
Posts: 78

Re: SAS Dataset with Variables Merge

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


Ask a Question
Discussion stats
  • 6 replies
  • 298 views
  • 5 likes
  • 6 in conversation