topic Re: Filling missing values with group and subgroup in SAS Programming

Filling missing values with group and subgroup

buckeyefisher — Fri, 03 Feb 2023 22:29:38 GMT

I have the following data

Case_number	Company	party_role
1	A	Plaintiff
1	B
1	C	Defendant
1	D
2	E	Plaintiff
2	F
2	G	Defendant
2	H

I want the output to look like this.

Case_number	Company	party_role
1	A	Plaintiff
1	B	Plaintiff
1	C	Defendant
1	D	Defendant
2	E	Plaintiff
2	F	Plaintiff
2	G	Defendant
2	H	Defendant

I tried following code but not working

data want;
set have;
retain repeat1;
if first.case_number then do;
if party_role ne '' then repeat1 = party_role; else do;
if party_role eq '' then party_role = repeat1; end; drop repeat1;
end;
run;

Re: Filling missing values with group and subgroup

Kurt_Bremser — Fri, 03 Feb 2023 23:09:54 GMT

data want;
set have;
retain repeat1;
if first.case_number
then do;
  if party_role ne ''
  then repeat1 = party_role;
  else do;
    if party_role eq '' then party_role = repeat1;
  end; 
  drop repeat1;
end;
run;

Do you see the logic mistake?

The log will also alert you to the fact that you tried to use a FIRST. variable without a corresponding BY.

BTW, DROP is a declarative statement. Putting it into a conditional branch is at best misleading.

Re: Filling missing values with group and subgroup

Ksharp — Sat, 04 Feb 2023 09:32:15 GMT

data want;
update have(obs=0) have;
by Case_number;
output;
run;

Re: Filling missing values with group and subgroup

Kurt_Bremser — Sat, 04 Feb 2023 12:04:24 GMT

You probably want this:

data want;
set have;
by case_number;
retain repeat1;
if first.case_number then repeat1 = "";
if party_role ne ""
then repeat1 = party_role;
else party_role = repeat1;
drop repeat1;
run;