topic Re: SAS Dataset with Variables Merge in SAS Procedures

SAS Dataset with Variables Merge

rkumar23 — Fri, 24 Oct 2014 12:57:35 GMT

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

Re: SAS Dataset with Variables Merge

stat_sas — Fri, 24 Oct 2014 14:01:00 GMT

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

var x y z;

run;

Re: SAS Dataset with Variables Merge

slchen — Fri, 24 Oct 2014 14:37:52 GMT

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;

Re: SAS Dataset with Variables Merge

mark_alexander_ct_gov — Fri, 24 Oct 2014 15:30:58 GMT

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

Re: SAS Dataset with Variables Merge

Ksharp — Sat, 25 Oct 2014 13:24:18 GMT

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

Re: SAS Dataset with Variables Merge

art297 — Sat, 25 Oct 2014 15:23:30 GMT

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;

Re: SAS Dataset with Variables Merge

rkumar23 — Mon, 27 Oct 2014 01:54:01 GMT

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