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

Comments

Popular posts from this blog

409.Can We Build a Reliable Emergency Services Analytics & Fraud Detection System in SAS While Identifying and Fixing Intentional Errors?

397.If a satellite has excellent signal strength but very high latency, can it still deliver good quality communication? Why or why not?A Sas Study

401.How Efficient Are Global Data Centers? A Complete SAS Analytics Study