coming two tables with reference table

[monday89]

I have a 1) reference table for medications and 2) patient level data for medications

Reference table for medications

med_id	med_name
1	ABC
2	DEF
3	GHI
4	JKL
5	MNO
6	PQR
7	STU
8	VWX
9	YZ

2) Patient Level Data

patientId	med_name	medication
123	ABC	1
123	GHI	1
123	JKL	1
123	PQR	1
456	DEF	1
456	GHI	1

What I want to do is that join both reference med and patient level but indicate the patients that are not on certain medications. 

patientId	med_name	medication
123	ABC	1
123	DEF	0
123	GHI	1
123	JKL	1
123	MNO	0
123	PQR	1
123	STU	0
123	VWX	0
123	YZ	0
456	ABC	0
456	DEF	1
456	GHI	1
456	JKL	0
456	MNO	0
456	PQR	0
456	STU	0
456	VWX	0
456	YZ	0

What I did was:

proc sql;

select a.*, b.med_name

from patient left join medtable a on med_name = med_name;quit;

[Tom]

So you want to expand the number of observations so there is one observation for every medication for each patient?

Do you want the MEDICATION variable to be copied over, but set to zero on the new observations?  Or are you looking for new variable that is 1 when the observation was in the patient table and zero when it wasn't?

Here is a straight forward way to do this. First make a skeleton of the final dataset that has all of the possible observations. Then merge it with your patient data.

proc sql ;
create table skeleton as
select patientid,med_name
from (select distinct patientid from patient)
   , (select distinct med_name from medtable)
order by patientid,med_name
;
quit;

data want;
  merge skeleton patient(in=in2);
  by patientid med_name;
  want = in2;
run;

       patient
Obs      Id       med_name    medication    want

  1      123        ABC            1          1
  2      123        DEF            .          0
  3      123        GHI            1          1
  4      123        JKL            1          1
  5      123        MNO            .          0
  6      123        PQR            1          1
  7      123        STU            .          0
  8      123        VWX            .          0
  9      456        ABC            .          0
 10      456        DEF            1          1
 11      456        GHI            1          1
 12      456        JKL            .          0
 13      456        MNO            .          0
 14      456        PQR            .          0
 15      456        STU            .          0
 16      456        VWX            .          0

[PGStats]

With SQL, use a cross join:

data names;
input med_id med_name :$3.;
datalines;
1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR
7 STU
8 VWX
9 YZ
;
data meds;
input patientId   med_name :$3.   medication;
datalines;
123     ABC     1
123     GHI     1
123     JKL     1
123     PQR     1
456     DEF     1
456     GHI     1
;

proc sql;
select 
	a.patientId,
	b.med_name,
	coalesce(c.medication, 0) as medication
from
(select distinct patientId from meds) as a cross join
(select distinct med_name from names) as b left join
meds as c on a.patientId=c.patientId and b.med_name=c.med_name;
quit;