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
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.
Show what you want please.
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.
Thank you so much ballardw! Your code worked for what I needed!!
dede1
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.