topic Re: Need help with syntax to use for accomplishing task in coding with SAS in SAS Programming

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

dede1 — Thu, 20 Jul 2017 21:44:15 GMT

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

. 1 9

8 2 7

. 2 .

. 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

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

Reeza — Thu, 20 Jul 2017 21:45:45 GMT

Show what you want please.

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

ballardw — Thu, 20 Jul 2017 23:37:56 GMT

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.

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

dede1 — Fri, 21 Jul 2017 01:38:02 GMT

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

dede1