topic Re: Give the same value in column, based on another column value in New SAS User

Give the same value in column, based on another column value

fcf — Wed, 16 Dec 2020 09:06:16 GMT

Hello!

I have these three columns: name, cod and id. Based on the same value in "name" on the previous row I want to provide the same cod on the id column, which is also the previous id/cod. When there is no cod associated with the name in any row, I want to keep it as blank.

So this is what I have:

Name	cod	id
Ana Maria Santos	123456789	123456789
Ana Maria Santos	123456789	123456789
Ana Maria Santos
João Pedro Costa
João Pedro Costa
Diogo Silva	987654321	987654321
Diogo Silva	987654321	987654321
Diogo Silva

And this is what I want:

Name	cod	id
Ana Maria Santos	123456789	123456789
Ana Maria Santos	123456789	123456789
Ana Maria Santos		123456789
João Pedro Costa
João Pedro Costa
Diogo Silva	987654321	987654321
Diogo Silva	987654321	987654321
Diogo Silva		987654321

Thank you in advance.

- I've tried merging, but it returns more rows (i don't know why);

- I've tried using first. and last.;

- I've tried using retain but it keeps on also filling the blank ones that I want to keep as blank.

Re: Give the same value in column, based on another column value

PeterClemmensen — Wed, 16 Dec 2020 09:16:17 GMT

Try this

data have;
input Name $ 1-16 cod id;
infile datalines missover;
datalines;
Ana Maria Santos 123456789 123456789
Ana Maria Santos 123456789 123456789
Ana Maria Santos                    
João Pedro Costa                    
João Pedro Costa                    
Diogo Silva      987654321 987654321
Diogo Silva      987654321 987654321
Diogo Silva                         
;

data want(drop=_id);
   set have;
   by Name notsorted;
   if first.Name then call missing(_id);
   if cod then _id = id;
   else         id = _id;
   retain _id;
run;

Result:

Name             cod       id 
Ana Maria Santos 123456789 123456789 
Ana Maria Santos 123456789 123456789 
Ana Maria Santos .         123456789 
João Pedro Costa .         . 
João Pedro Costa .         . 
Diogo Silva      987654321 987654321 
Diogo Silva      987654321 987654321 
Diogo Silva      .         987654321

Re: Give the same value in column, based on another column value

fcf — Wed, 16 Dec 2020 09:21:32 GMT

Thank you 🙂 It worked. I just don't understand why I now have '.' in the output in the ones that were blank

Re: Give the same value in column, based on another column value

Kurt_Bremser — Wed, 16 Dec 2020 09:26:31 GMT

That's why we request that example data is posted in a data step with datalines. Only then will the attributes of columns be clear and unambiguous.

I guess(!) that in your data cod and id are character, but that was not really clear from the quasi-screenshot you posted.

Re: Give the same value in column, based on another column value

ballardw — Wed, 16 Dec 2020 09:29:33 GMT

@fcf wrote:
Thank you 🙂 It worked. I just don't understand why I now have '.' in the output in the ones that were blank

The example that @PeterClemmensen used numeric values because you did not indicate one way or the other. The default missing character . is displayed when the value is numeric.

Re: Give the same value in column, based on another column value

fcf — Wed, 16 Dec 2020 09:41:48 GMT

ok thank you, next time I will say the type of variables! my variable still remains as character but now it has that point.

Re: Give the same value in column, based on another column value

Kurt_Bremser — Wed, 16 Dec 2020 09:48:29 GMT

That is because _id will be numeric, as the data step compiler will assume that when the variable is first encountered in the CALL MISSING.

One needs to take care of all of that:

data have;
input Name $17. (cod id) (:$10.);
infile datalines truncover;
datalines;
Ana Maria Santos 123456789 123456789
Ana Maria Santos 123456789 123456789
Ana Maria Santos                    
João Pedro Costa                    
João Pedro Costa                    
Diogo Silva      987654321 987654321
Diogo Silva      987654321 987654321
Diogo Silva                         
;

data want(drop=_id);
   set have;
   by Name notsorted;
   length _id $10;
   if first.Name then call missing(_id);
   if cod then _id = id;
   else         id = _id;
   retain _id;
run;

I also had to change the informat for Name because of the UTF character in João

Re: Give the same value in column, based on another column value

fcf — Wed, 16 Dec 2020 09:54:54 GMT

Thank you so much, it worked clearly! Have a nice day and thank you, next time I will write the type of variables i have.

Re: Give the same value in column, based on another column value

Kurt_Bremser — Wed, 16 Dec 2020 09:57:31 GMT

Do not try to "describe" your data. POST A DATA STEP, and all will be well. Code never lies.