Need the correct output

[Aexor]

Hi All,

I have input like this, Where information for particular id is segregated. 

input
id   name   lastname  number
1  John                         .
1             Deo               . 
1                                 8888

I need Output  like this , where for a particular id , all information should come in single row.

id  name  lastname   number
1  John    Deo            8888

Please help me  in solving this.

Thanks!

[PeterClemmensen]

Do you have more variables than 3 in your actual data?

[Aexor]

Yes, posted these as sample only

[PeterClemmensen]

Ok. Does my code below work for you?

[Aexor]

let me check 🙂 I will id more ids with different combination and try

[PeterClemmensen · Accepted solution]

See if this works for you. I added an extra ID.

data have;
input id name $ lastname $ number;
infile datalines dlm = '|';
datalines;
1|John|   |.    
1|    |Deo|.    
1|    |   |8888 
2|John|   |.    
2|    |Deo|.    
2|    |   |8888 
;

data want;
   update have(obs = 0) have;
   by id;
   if last.ID then output;
run;

[Aexor]

Thanks. This code worked. Just a small request . Could you please explain how this update statement worked

[PeterClemmensen]

Sure. This is a weel known technique among SAS programmers to replace missing obs with previous non-missing values. 

The key to this trick is the UPDATE Statement. I use have(obs=0) as the master data set to load the relevant variables and no observations into the PDV. Next, I also use have(all observations included) as the transaction data set and update the master data set from this. Also, I have to use an explicit OUTPUT Statement, because by default SAS only updates the first observation in each group and outputs that.

As per the Update Statemnet Doc:

By default, the UPDATEMODE=MISSINGCHECK option is in effect, so missing values in the transaction data set do not replace existing values in the master data set. If you want missing values in the transaction data set to replace existing values in the master data set, specify the UPDATEMODE=NOMISSINGCHECK option in the UPDATE statement.

[Aexor]

Thanks a ton!!!!!!!!!!!!

[PeterClemmensen]

@Aexor  Anytime 🙂

Please remember to mark my answer as accepted solution.

[Aexor]

Yes. Done that. Just one more information .

I have used this code and this also worked fine
data have;
input id name $ lastname $ number;
infile datalines dlm = '|';
datalines;
1|John| |.
1| |Deo|.
1| | |8888
2|John| |.
2| |Deo|.
2| | |8888
;
run;


data want;
update have(obs=0) have;
by id;
/* if last.ID then output; */
run;

Could you please tell me significance of last.id in your code.