CODEWITHSUNDEEP
sas
smackersz88
smackersz88

Reputation: 93

Populating result variable based on multiple records

I have this parameter code specification

"Create a record with PARAMCD set to "DLQI7" for every record in SDTM.QS where QS.QSTESTCD equals "DLQI7". If there is a corresponding (by USUBJID, VISITNUM) record where QS.QSTESTCD equals "DLQI7SCO" and QS.QSSTRESN is not missing then set AVAL to the non-missing value of QS.QSSTRESN from this record. Else, if QS.QSORRES equals "00" on the original record where QS.QSTESTCD equals "DLQI7", then set AVAL to 0."

Here is a sample of my data with the relevant columns

USUBJID VISITNUM QSTESTCD   QSSTRESN    QSORRES
1001    2        DLQI7            0           0
1001    4        DLQI7            0           0
1001    5        DLQI7            0           0
1001    6        DLQI7            0           0
1001    2        DLQI7SCO         0           0
1001    4        DLQI7SCO         0           0
1001    5        DLQI7SCO         0           0
1001    6        DLQI7SCO         0           0
1002    2        DLQI7            0           0
1002    4        DLQI7            0           0
1002    5        DLQI7            0           0
1002    6        DLQI7            0           00
1002    2        DLQI7SCO         1           1
1002    4        DLQI7SCO         1           1
1002    5        DLQI7SCO         1           1
1002    6        DLQI7SCO       
1002    2        DLQI7                        0
1002    10       DLQI7                        0 
1002    2        DLQI7SCO         2           2
1002    10       DLQI7SCO         0           0
1003    2        DLQI7            0           0
1003    2        DLQI7SCO         1           1
1004    2        DLQI7            0           00
1004    4        DLQI7            0           00
1004    5        DLQI7            0           00
1004    2        DLQI7SCO       
1004    4        DLQI7SCO       
1004    5        DLQI7SCO

What's the best approach here?. I have to remove the DLQI7SCO or DLQI7 records depending on if they are non-missing as it has to be one record where QS.QSTESTCD equals "DLQI7" for each subject. Should I use proc transpose for the DLQI7SCO records and then maybe use a combination of nmiss and coalesce to produce AVAL?

Upvotes: 0

Views: 72

Answers (1)

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

Let's break this down step by step and translate each statement into a piece of code.

1. Create a record with PARAMCD set to "DLQI7" for every record in SDTM.QS where QS.QSTESTCD equals "DLQI7"

if(QSTESTCD = 'DLQI7') then PARAMCD = QSTESTCD;

2. If there is a corresponding (by USUBJID, VISITNUM) record where QS.QSTESTCD equals "DLQI7SCO" and QS.QSSTRESN is not missing then set AVAL to the non-missing value of QS.QSSTRESN from this record.

if(QSTESTCD = 'DLQI7SCO' AND NOT missing(QSSTRESN) ) then AVAL = QSSTRESN

3. Else, if QS.QSORRES equals "00" on the original record where QS.QSTESTCD equals "DLQI7", then set AVAL to 0."

else if(QSORRES = '00' AND QSTESTCD = 'DLQI7') then AVAL = 0;

The data looks like it is sorted in a particular order. We'll leave it in that order.

This yields the following program:

data want;
    set have;
    by usubjid qtestcd visitnum notsorted;
 
    if(QSTESTCD = 'DLQI7') then PARAMCD = QSTESTCD;

    if(QSTESTCD = 'DLQI7SCO' AND NOT missing(QSSTRESN) ) then AVAL = QSSTRESN;
        else if(QSORRES = '00' AND QSTESTCD = 'DLQI7') then AVAL = 0;
run;

If you can provide me with some feedback on if this is the right approach then that would be helpful, but this is my best direct translation of the instructions.

Upvotes: 2

Related Questions