Inserting Missing Rows

[djbateman]

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

[Kurt_Bremser · Accepted solution]

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

[djbateman]

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.

[Ksharp]

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;