Need help with syntax to use for accomplishing task in coding with SAS

[dede1]

Hello, I would like to know if anyone out there is able to help me write the SAS code for the following task:

Here is the scenario:

(a) My data has 3 variables x,y & z

(b) All of my y fields are populated but only some of the x and z fields are populated. As long as an x field has a value, the z field also has to have a value.

(c) Some of the values in the y field are the same which means that their x and y values will always be the same as well.

HERE'S HOW MY DATA LOOKS...

x                         y                         z

.                          1                         .

6                         1                        9

6                         1                        9

.                          1                        9

.                          1                        9

8                         2                        7

8                         2                        7

.                          2                        .

.                          2                        .

.                          2                        .

.                          3                        .

.                          3                        .

.                          3                        .

5                         3                        4 

HERE'S MY QUESTION:

How do I write a code so that I do not need to manually input all the missing x and z values in my dataset given that all of my y's are populated and I have values for some of their x and z's???

Hope someone out there can help me...

Thanks,

dede1

ps - I'm using v9.4

[Reeza]

Show what you want please.

[ballardw · Accepted solution]

Something like this perhaps?

data have;
 input x                         y                         z;
datalines;
.                          1                         .
6                         1                        9
6                         1                        9
.                          1                        9
.                          1                        9
8                         2                        7
8                         2                        7
.                          2                        .
.                          2                        .
.                          2                        .
.                          3                        .
.                          3                        .
.                          3                        .
5                         3                        4 
;
run;

proc sql;
   create table want as
   select a.y, b.x,b.z
   from have as a
        left join
        (select y, max(x) as x, max(z) as z
         from have
         group by y) as b
      on a.y=b.y;
quit;

Note that if your variables x and z are character the MAX function may not return what I am guessing you want.

[dede1]

Thank you so much ballardw! Your code worked for what I needed!!

dede1