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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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


Accepted Solutions
Solution
a month ago
Super User
Posts: 10,500

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

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.

 

View solution in original post


All Replies
Super User
Posts: 17,829

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

Show what you want please.

Solution
a month ago
Super User
Posts: 10,500

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

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.

 

New Contributor
Posts: 2

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

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

 

dede1

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 127 views
  • 1 like
  • 3 in conversation