topic Need the correct output in SAS Programming

Need the correct output

Aexor — Thu, 15 Sep 2022 06:43:06 GMT

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!

Re: Need the correct output

PeterClemmensen — Thu, 15 Sep 2022 06:46:47 GMT

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

Re: Need the correct output

PeterClemmensen — Thu, 15 Sep 2022 06:51:39 GMT

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;

Re: Need the correct output

Aexor — Thu, 15 Sep 2022 06:51:54 GMT

Yes, posted these as sample only

Re: Need the correct output

PeterClemmensen — Thu, 15 Sep 2022 06:53:26 GMT

Ok. Does my code below work for you?

Re: Need the correct output

Aexor — Thu, 15 Sep 2022 07:13:06 GMT

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

Re: Need the correct output

Aexor — Thu, 15 Sep 2022 09:22:10 GMT

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

Re: Need the correct output

PeterClemmensen — Thu, 15 Sep 2022 09:26:14 GMT

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.

Re: Need the correct output

Aexor — Thu, 15 Sep 2022 09:37:48 GMT

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

Re: Need the correct output

PeterClemmensen — Thu, 15 Sep 2022 09:40:47 GMT

@Aexor Anytime 🙂

Please remember to mark my answer as accepted solution.

Re: Need the correct output

Aexor — Thu, 15 Sep 2022 10:20:36 GMT

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.