BookmarkSubscribeRSS Feed
Dontik
Obsidian | Level 7

Hi

I have a following problem, what I have right now is:

ID | V1 | V2 | V3 | 
----------------------
1   | x   |       |      | 

1   | x   |       |      | 

1   | x   |       |      | 

1   |      | x    |      | 

1   |      | x    |      | 

1   |      | x    |      |

1   |      |       |  x  | 

1   |      |       |  x  |

1   |      |       |  x  |

2   | x   |       |      |

2   |      | x    |      |

2   |      |       |  x  |

3   | x   |       |      | 

3   | x   |       |      | 

3   |      | x    |      |

3   |      | x    |      | 

3   |      |       | x   |

3   |      |       | x   | 

4   | x   |       |      | 

4   | x   |       |      | 

4   |      | x    |      |

4   |      | x    |      | 

4   |      |       | x   |

4   |      |       | x   |  

 

and other variants.

What I would like to have:

1   | x   | x    | x    | 

1   | x   | x    | x    | 

1   | x   | x    | x    | 

2   | x   | x    | x    |

3   | x   | x    | x    | 

3   | x   | x    | x    | 

4   | x   | x    | x    | 

4   | x   | x    | x    | 

 
I'd be grateful!

2 REPLIES 2
terrencetang
Calcite | Level 5

You can try the following code, hope it can help you.

 

Assuming your datatset is called "a":

 

data V1;
set a;
where V1 is not missing;
keep ID V1;
run;

data V2;
set a;
where V2 is not missing;
keep ID V2;
run;

data V3;
set a;
where V3 is not missing;
keep ID V3;
run;

data a;
merge V1 V2 V3;
by ID;
run;

proc datasets nolist;

delete V1 V2 V3;

run;

-------------------------------------------------------------------------------------

 

If you have more variables in the dataset similar to this pattern, just create more V<n> datasets in the same way, and then merging them in the data step.

 

The proc dataset is used to delete those workings.

 

 

Patrick
Opal | Level 21

...or this way:


data want;
  merge 
    have(keep=id v1 where=(not missing(v1)))
    have(keep=id v2 where=(not missing(v2)))
    have(keep=id v3 where=(not missing(v3)))
    ;
  by id;
run;

The code assumes that - like in your sample data - per id you've got always the same number of rows with a populated v1, v2 and v3. 

 

If this should not be the case with your real data then please explain to us how the desired data set should look like in such a case.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 957 views
  • 1 like
  • 3 in conversation