topic Copy cell data into blank cells by another variable in SAS Programming

Copy cell data into blank cells by another variable

kippy_kips — Wed, 21 Dec 2022 04:32:13 GMT

I have columns named dairy_ee, fruit_ee, oils_ee, etc. They are either a 0 or 1 but there are some blank spaces. I want to fill these columns with a 0 or 1 based on another variable group.

It looks something like this:

Group	dairy_ee	fruit_ee	oils_ee
1	0	1	0
1	.	.	.
2	0	1	0
2	.	.	.
3	1	1	0
3	.	.	.
4	0	0	0
4	.	.	.
4	.	.	.

Thank you!

Re: Copy cell data into blank cells by another variable

mkeintz — Wed, 21 Dec 2022 05:03:36 GMT

@kippy_kips wrote:

I have columns named dairy_ee, fruit_ee, oils_ee, etc. They are either a 0 or 1 but there are some blank spaces. I want to fill these columns with a 0 or 1 based on another variable group.

And what is the specific relationship between the variable group and the intended blank-replacement values? That is what is the rule you want to applhy?

Re: Copy cell data into blank cells by another variable

Astounding — Wed, 21 Dec 2022 12:49:47 GMT

Here's a program that will work based on the limited amount of data shown:

dara want;
update have (obs=0) have;
by group;
output;
run;

It requires that the incoming data is sorted by GROUP, and that a GROUP never begins with a blank row.

Re: Copy cell data into blank cells by another variable

kippy_kips — Wed, 21 Dec 2022 17:02:38 GMT

I have other columns that I don't want to fill blanks in, can you specify columns?

Re: Copy cell data into blank cells by another variable

kippy_kips — Wed, 21 Dec 2022 17:03:19 GMT

I want each empty to be filled with the 0 or 1 that is already in that group

Re: Copy cell data into blank cells by another variable

Astounding — Wed, 21 Dec 2022 19:45:57 GMT

Yes, but you will need to add a statement to the program that controls which variables don't get replaced. Just before the OUTPUT statement:

set have (keep = list of variables to keep as is);