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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.