182.CREATING A UNIQUE CYBER-THERAPY ADaM DATASET WITH MORE THAN 15 OBSERVATIONS USING PROC SQL | PROC PRINT | PROC MEANS | PROC SORT | PROC FORMAT | SAS MACROS IN A STEP-BY-STEP APPROACH FOR CLINICAL TRIAL ANALYSIS

CREATING A UNIQUE CYBER-THERAPY ADaM DATASET WITH MORE THAN 15 OBSERVATIONS USING PROC SQL | PROC PRINT | PROC MEANS | PROC SORT | PROC FORMAT | SAS MACROS IN A  STEP-BY-STEP APPROACH FOR CLINICAL TRIAL ANALYSIS

/*Creating an ADaM (Analysis Data Model) dataset from scratch using SQL and macros in SAS*/

/*A clinical trial studying the effect of a cyber-cognitive therapy on digital stress*/

Step 1: Study Design Overview

Therapy Goal: Reduce digital stress using cyber-cognitive training.

Key Parameters:

Study ID: CYBER001

Arms: Control (Placebo) and Treatment (Cognitive App)

Duration: 10 days

Subjects: 18 unique participants

Endpoint: Reduction in daily anxiety score (scale: 0–10)

Step 2: Simulating Raw SDTM-Like Data

Simulating data for the SDTM domains:

DM (Demographics)

QS (Questionnaires - daily stress score)

2.1 Create DM (Demographics) Domain

data sdm_dm;

    input STUDYID $ USUBJID $ AGE SEX $ ARMCD $;

    datalines;

CYBER001 SUBJ001 25 M TRT

CYBER001 SUBJ002 33 F TRT

CYBER001 SUBJ003 29 M TRT

CYBER001 SUBJ004 41 F TRT

CYBER001 SUBJ005 35 M TRT

CYBER001 SUBJ006 27 F TRT

CYBER001 SUBJ007 38 M TRT

CYBER001 SUBJ008 31 F TRT

CYBER001 SUBJ009 40 M TRT

CYBER001 SUBJ010 24 F TRT

CYBER001 SUBJ011 26 M PLC

CYBER001 SUBJ012 39 F PLC

CYBER001 SUBJ013 37 M PLC

CYBER001 SUBJ014 28 F PLC

CYBER001 SUBJ015 36 M PLC

CYBER001 SUBJ016 30 F PLC

CYBER001 SUBJ017 42 M PLC

CYBER001 SUBJ018 34 F PLC

;

run;

proc print;run;

Output:

Obs	STUDYID	USUBJID	AGE	SEX	ARMCD
1	CYBER001	SUBJ001	25	M	TRT
2	CYBER001	SUBJ002	33	F	TRT
3	CYBER001	SUBJ003	29	M	TRT
4	CYBER001	SUBJ004	41	F	TRT
5	CYBER001	SUBJ005	35	M	TRT
6	CYBER001	SUBJ006	27	F	TRT
7	CYBER001	SUBJ007	38	M	TRT
8	CYBER001	SUBJ008	31	F	TRT
9	CYBER001	SUBJ009	40	M	TRT
10	CYBER001	SUBJ010	24	F	TRT
11	CYBER001	SUBJ011	26	M	PLC
12	CYBER001	SUBJ012	39	F	PLC
13	CYBER001	SUBJ013	37	M	PLC
14	CYBER001	SUBJ014	28	F	PLC
15	CYBER001	SUBJ015	36	M	PLC
16	CYBER001	SUBJ016	30	F	PLC
17	CYBER001	SUBJ017	42	M	PLC
18	CYBER001	SUBJ018	34	F	PLC

2.2 Create QS (Daily Anxiety Scores)

Use a macro to simulate 10 days of anxiety scores per subject.

%macro generate_qs_data;

data sdm_qs;

    length STUDYID USUBJID $10 QSTESTCD $10 QSTEST $50 VISIT $10;

    format QSDTC date9.;

    retain QSTESTCD 'DSTRS' QSTEST 'Daily Stress Score';

    do subj = 1 to 18;

        usubjid = cats('SUBJ', put(subj, z3.));

        studyid = 'CYBER001';

        do day = 1 to 10;

            visit = cats("Day", day);

            qsdtc = '01JAN2024'd + day - 1;

            score = ceil(ranuni(1234) * 10);  /* Random score 1–10 */

            output;

        end;

    end;

run;

proc print;run;

%mend;

%generate_qs_data

Output:

Obs	STUDYID	USUBJID	QSTESTCD	QSTEST	VISIT	QSDTC	subj	day	score
1	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day1	01JAN2024	1	1	3
2	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day2	02JAN2024	1	2	1
3	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day3	03JAN2024	1	3	4
4	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day4	04JAN2024	1	4	1
5	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day5	05JAN2024	1	5	3
6	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day6	06JAN2024	1	6	1
7	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day7	07JAN2024	1	7	1
8	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day8	08JAN2024	1	8	2
9	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day9	09JAN2024	1	9	5
10	CYBER001	SUBJ001	DSTRS	Daily Stress Score	Day10	10JAN2024	1	10	2
11	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day1	01JAN2024	2	1	1
12	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day2	02JAN2024	2	2	5
13	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day3	03JAN2024	2	3	1
14	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day4	04JAN2024	2	4	10
15	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day5	05JAN2024	2	5	10
16	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day6	06JAN2024	2	6	8
17	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day7	07JAN2024	2	7	5
18	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day8	08JAN2024	2	8	4
19	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day9	09JAN2024	2	9	4
20	CYBER001	SUBJ002	DSTRS	Daily Stress Score	Day10	10JAN2024	2	10	6

Step 3: Define ADaM Dataset Structure (ADQS)

Derive ADQS, where:

Each record is a subject-day combination

Include:

PARAMCD: DSTRS

AVAL: Actual score

CHG: Change from baseline

BASE: Day 1 score

TRTFL: Treatment flag (Y/N)

Step 4: Derive ADaM Variables Using SQL & Macros

4.1 Combine DM and QS

proc sql;

    create table adqs_base as

    select 

        q.studyid,

        q.usubjid,

        d.armcd,

        d.age,

        d.sex,

        q.qstestcd as paramcd,

        q.qstest as param,

        q.visit,

        q.qsdtc,

        q.score as aval

    from sdm_qs q

    inner join sdm_dm d

    on q.usubjid = d.usubjid;

quit;

proc print data=adqs_base;

run;

Output:

Obs	STUDYID	USUBJID	ARMCD	AGE	SEX	paramcd	param	VISIT	QSDTC	aval
1	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day1	01JAN2024	3
2	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day2	02JAN2024	1
3	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day3	03JAN2024	4
4	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day4	04JAN2024	1
5	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day5	05JAN2024	3
6	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day6	06JAN2024	1
7	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day7	07JAN2024	1
8	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day8	08JAN2024	2
9	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day9	09JAN2024	5
10	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day10	10JAN2024	2
11	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day1	01JAN2024	1
12	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day2	02JAN2024	5
13	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day3	03JAN2024	1
14	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day4	04JAN2024	10
15	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day5	05JAN2024	10
16	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day6	06JAN2024	8
17	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day7	07JAN2024	5
18	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day8	08JAN2024	4
19	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day9	09JAN2024	4
20	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day10	10JAN2024	6

4.2 Identify Baseline (Day1)

proc sql;

    create table baseline as

    select usubjid, aval as base

    from adqs_base

    where visit = "Day1";

quit;

proc print data=baseline;

run;

Output:

Obs	USUBJID	base
1	SUBJ001	3
2	SUBJ002	1
3	SUBJ003	3
4	SUBJ004	8
5	SUBJ005	5
6	SUBJ006	4
7	SUBJ007	7
8	SUBJ008	9
9	SUBJ009	3
10	SUBJ010	9
11	SUBJ011	1
12	SUBJ012	3
13	SUBJ013	5
14	SUBJ014	8
15	SUBJ015	6
16	SUBJ016	8
17	SUBJ017	3
18	SUBJ018	5

4.3 Merge Baseline and Calculate Change from Baseline

proc sql;

    create table adqs_step2 as

    select a.*, b.base, (a.aval - b.base) as chg

    from adqs_base a

    left join baseline b

    on a.usubjid = b.usubjid;

quit;

proc print data=adqs_step2;

run;

Output:

Obs	STUDYID	USUBJID	ARMCD	AGE	SEX	paramcd	param	VISIT	QSDTC	aval	base	chg
1	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day4	04JAN2024	1	3	-2
2	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day2	02JAN2024	1	3	-2
3	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day6	06JAN2024	1	3	-2
4	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day1	01JAN2024	3	3	0
5	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day5	05JAN2024	3	3	0
6	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day3	03JAN2024	4	3	1
7	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day8	08JAN2024	2	3	-1
8	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day9	09JAN2024	5	3	2
9	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day10	10JAN2024	2	3	-1
10	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day7	07JAN2024	1	3	-2
11	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day5	05JAN2024	10	1	9
12	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day4	04JAN2024	10	1	9
13	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day2	02JAN2024	5	1	4
14	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day6	06JAN2024	8	1	7
15	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day10	10JAN2024	6	1	5
16	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day1	01JAN2024	1	1	0
17	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day8	08JAN2024	4	1	3
18	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day3	03JAN2024	1	1	0
19	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day7	07JAN2024	5	1	4
20	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day9	09JAN2024	4	1	3

Step 5: Add Derived Variables Using Macro

Use a macro to add:

TRTFL: Treatment flag (Y/N)

AVISITN: Numeric visit

ANL01FL: Analysis flag for primary endpoint (Day10)

%macro derive_adqs;

data adqs_final;

    set adqs_step2;

    length trtfl $1 anl01fl $1;

    if armcd = 'TRT' then trtfl = 'Y';

    else trtfl = 'N';

    avisitn = input(compress(visit, ,'kd'), best.);

    if visit = 'Day10' then anl01fl = 'Y';

    else anl01fl = '';

run;

proc print;run;

%mend;

%derive_adqs

Output

Obs	STUDYID	USUBJID	ARMCD	AGE	SEX	paramcd	param	VISIT	QSDTC	aval	base	chg	trtfl	anl01fl	avisitn
1	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day4	04JAN2024	1	3	-2	Y		4
2	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day2	02JAN2024	1	3	-2	Y		2
3	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day6	06JAN2024	1	3	-2	Y		6
4	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day1	01JAN2024	3	3	0	Y		1
5	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day5	05JAN2024	3	3	0	Y		5
6	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day3	03JAN2024	4	3	1	Y		3
7	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day8	08JAN2024	2	3	-1	Y		8
8	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day9	09JAN2024	5	3	2	Y		9
9	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day10	10JAN2024	2	3	-1	Y	Y	10
10	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day7	07JAN2024	1	3	-2	Y		7
11	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day5	05JAN2024	10	1	9	Y		5
12	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day4	04JAN2024	10	1	9	Y		4
13	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day2	02JAN2024	5	1	4	Y		2
14	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day6	06JAN2024	8	1	7	Y		6
15	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day10	10JAN2024	6	1	5	Y	Y	10
16	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day1	01JAN2024	1	1	0	Y		1
17	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day8	08JAN2024	4	1	3	Y		8
18	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day3	03JAN2024	1	1	0	Y		3
19	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day7	07JAN2024	5	1	4	Y		7
20	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day9	09JAN2024	4	1	3	Y		9

 Final Dataset: ADQS

 proc print data=adqs_final(obs=20);  /* Display first 20 records */

    title "Final ADQS Dataset - First 20 Observations";

run;

Output:

Final ADQS Dataset - First 20 Observations

Obs	STUDYID	USUBJID	ARMCD	AGE	SEX	paramcd	param	VISIT	QSDTC	aval	base	chg	trtfl	anl01fl	avisitn
1	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day4	04JAN2024	1	3	-2	Y		4
2	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day2	02JAN2024	1	3	-2	Y		2
3	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day6	06JAN2024	1	3	-2	Y		6
4	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day1	01JAN2024	3	3	0	Y		1
5	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day5	05JAN2024	3	3	0	Y		5
6	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day3	03JAN2024	4	3	1	Y		3
7	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day8	08JAN2024	2	3	-1	Y		8
8	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day9	09JAN2024	5	3	2	Y		9
9	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day10	10JAN2024	2	3	-1	Y	Y	10
10	CYBER001	SUBJ001	TRT	25	M	DSTRS	Daily Stress Score	Day7	07JAN2024	1	3	-2	Y		7
11	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day5	05JAN2024	10	1	9	Y		5
12	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day4	04JAN2024	10	1	9	Y		4
13	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day2	02JAN2024	5	1	4	Y		2
14	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day6	06JAN2024	8	1	7	Y		6
15	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day10	10JAN2024	6	1	5	Y	Y	10
16	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day1	01JAN2024	1	1	0	Y		1
17	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day8	08JAN2024	4	1	3	Y		8
18	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day3	03JAN2024	1	1	0	Y		3
19	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day7	07JAN2024	5	1	4	Y		7
20	CYBER001	SUBJ002	TRT	33	F	DSTRS	Daily Stress Score	Day9	09JAN2024	4	1	3	Y		9

Step 6: Validate and Summarize

Generate a summary report:

proc sql;

    select armcd, visit, 

           count(*) as n, 

           mean(aval) as mean_score,

           mean(chg) as mean_change

    from adqs_final

    group by armcd, visit;

quit;

Output:

ARMCD	VISIT	n	mean_score	mean_change
PLC	Day1	8	4.875	0
PLC	Day10	8	6.25	1.375
PLC	Day2	8	4.75	-0.125
PLC	Day3	8	6.25	1.375
PLC	Day4	8	6	1.125
PLC	Day5	8	7.625	2.75
PLC	Day6	8	7	2.125
PLC	Day7	8	6.25	1.375
PLC	Day8	8	6	1.125
PLC	Day9	8	3.375	-1.5
TRT	Day1	10	5.2	0
TRT	Day10	10	5.7	0.5
TRT	Day2	10	5.4	0.2
TRT	Day3	10	3.4	-1.8
TRT	Day4	10	6.7	1.5
TRT	Day5	10	5.1	-0.1
TRT	Day6	10	6.1	0.9
TRT	Day7	10	3.9	-1.3
TRT	Day8	10	5.3	0.1
TRT	Day9	10	6.2	1

Summary

Step Description

1         Defined a unique clinical study focused on digital stress

2         Simulated SDTM domains (DM, QS) for 18 subjects

3         Designed ADQS ADaM dataset with key derived variables

4         Used SQL joins and calculations to derive BASE, CHG

5            Applied SAS Macros to add flags and visit numbers

6            Validated with summary stats

PRACTICE AND COMMENT YOUR CODE: 

--->PLEASE FOLLOW OUR BLOG FOR MORE UPDATES.

PLEASE FOLLOW OUR TELEGRAM CHANNEL CLICK HERE

PLEASE FOLLOW OUR FACEBOOK PAGE  CLICK HERE

PLEASE FOLLOW OUR INSTAGRAM PAGE CLICK HERE