Hi,
I'm having an issue with my code
proc sql;
create table soc_pt_pairs_AE as
select distinct aesoc, aedecod
from adtte01
where not missing(aesoc) and not missing(aedecod) and ANL01FL = 'Y';
quit;
/* Get unique list of subjects */
proc sql;
create table unique_subjects as
select distinct usubjid
from adtte01;
quit;
/* Create a cartesian product of subjects and SOC/PT pairs */
proc sql;
create table subject_soc_pt as
select a.usubjid, b.aesoc, b.aedecod
from unique_subjects a, soc_pt_pairs_AE b
order by usubjid,aesoc,aedecod;
quit;
proc sort data=adtte01; by usubjid aesoc aedecod; run;
/* Merge with adtte01 to create TEAE with all columns retained */
data TEAE (drop=aesoc aedecod);
merge subject_soc_pt(in=a)
adtte01(in=b);
by usubjid aesoc aedecod;
length PARAMCD $ 20 PARAM $ 200 PARQUAL1 $ 100 PARQUAL2 $ 100;
PARAMCD = "AE";
PARAM = "Adverse Events";
PARAMN = 1;
PARQUAL1 = aesoc;
PARQUAL2 = aedecod;
/* Set CNSR: 0 if match found, 1 otherwise */
if a then CNSR = (not b);
run;
The aim of this code is to create a list of system organ class (AESOC) and preferred term (aedecod) based on a certain criteria. (ANL01FL="Y"). I need to retain all occurences of aesoc and pt for each subject. so say there is 10 unique soc and pt it would be 10 records per each subject. If a subject has one of these events occur to them then it is an event and is assigned cnsr = 0 and if not cnsr= 1. The issue with my code is it is creating 100 records for 10 subjects but only retains the variables from adtte01 where a subject has a record. I need it to populate the same study information variables for the other 9 records created too and for all 10 if an event didn't occur. How do i do this? I tried using arrays and dictionaries/sasvhelp to dynamically generate which got messy because sas doesn't allow a mixture of character and numeric variables and there are are too many variables to do a manual retain last. approach
