topic Re: Inserting Missing Rows in SAS Programming

Inserting Missing Rows

djbateman — Thu, 30 Oct 2025 21:11:10 GMT

I am certain that this will be a simple solution, but I cannot see it. I have a large lab dataset. I have simplified it into the table below. However, there should be both a Central and a Local source (SOURCE) for each lab test (LBTESTCD). I want to ensure that there are 2 lines per test but also that the corresponding RANGE and UNITS are retained. Is there some kind of simple SQL code that I might not be familiar with that could handle this?

Current table:

SUBJID	SOURCE	LBCAT	LBTESTCD	RANGE	UNITS
001-001-001	Central	Serum Chemistry	ALB	3.62-5.21	g/dL
001-001-001	Central	Serum Chemistry	ALP	33.0-111.0	IU/L
001-001-001	Local	Serum Chemistry	ALP	33.0-111.0	IU/L
001-001-001	Local	Serum Chemistry	ALT	10.0-39.0	IU/L

SAS code to create the above table:

proc sql;
	create table labs (SUBJID char(11), SOURCE char(10), LBCAT char(30), LBTESTCD char(10), RANGE char(25), UNITS char(10));
	insert into labs (subjid, source, lbcat lbtestcd, range, units)
		values('001-001-001','Central','Serum Chemistry','ALB','3.62-5.21','g/dL')
		values('001-001-001','Central','Serum Chemistry','ALP','33.0-111.0','IU/L')
		values('001-001-001','Local','Serum Chemistry','ALP','33.0-111.0','IU/L')
		values('001-001-001','Local','Serum Chemistry','ALT','10.0-39.0','IU/L');
quit;

Desired table:

SUBJID	SOURCE	LBCAT	LBTESTCD	RANGE	UNITS
001-001-001	Central	Serum Chemistry	ALB	3.62-5.21	g/dL
001-001-001	Local	Serum Chemistry	ALB	3.62-5.21	g/dL
001-001-001	Central	Serum Chemistry	ALP	33.0-111.0	IU/L
001-001-001	Local	Serum Chemistry	ALP	33.0-111.0	IU/L
001-001-001	Central	Serum Chemistry	ALT	10.0-39.0	IU/L
001-001-001	Local	Serum Chemistry	ALT	10.0-39.0	IU/L

Re: Inserting Missing Rows

Kurt_Bremser — Thu, 30 Oct 2025 21:19:57 GMT

data want;
set labs;
by subjid lbtestcd;
if first.lbtestcd;
source = "Central";
output;
source = "Local";
output;
run;

Re: Inserting Missing Rows

djbateman — Thu, 30 Oct 2025 21:57:49 GMT

This is fabulous. I knew it would be something simple. I had to make a few tweaks for my detailed situation, but this was a major help. Thank you so much.

Re: Inserting Missing Rows

Ksharp — Fri, 31 Oct 2025 01:48:26 GMT

If you don't want to type 'Local' and 'Central' by hand,you could try this SQL code.

data have;
infile cards expandtabs truncover;
input SUBJID :$40.	SOURCE	 :$40. LBCAT	 &$40. LBTESTCD  :$40.	RANGE  :$40.	UNITS  :$40.;
cards;
001-001-001	Central   Serum Chemistry	  ALB	3.62-5.21	g/dL
001-001-001	Central	  Serum Chemistry	  ALP	33.0-111.0	IU/L
001-001-001	Local	  Serum Chemistry	  ALP	33.0-111.0	IU/L
001-001-001	Local	  Serum Chemistry	  ALT	10.0-39.0	IU/L
;


proc sql;
create table want as
select SUBJID,LBTESTCD,SOURCE,max(LBCAT) as LBCAT,max(RANGE) as RANGE,max(UNITS) as UNITS from
(
select a.*,b.LBCAT,b.RANGE,b.UNITS from
(select * from
(select distinct SUBJID,LBTESTCD from have),(select distinct SOURCE from have)) as a
natural left join 
(select * from have) as b
)
group by SUBJID,LBTESTCD
order by SUBJID,LBTESTCD;
quit;