How to generate suppdm dataset from dm dataset?

[pdhokriya]

Hi Reader,

How should I generate suppdm when for each subject I have multiple race and randomization dates.?

DM dataset : ( all charcter datatype)

SUBJID	RANDTC	RACE
1001	2020-07-16T11:50:40	ASIAN
1002		WHITE
1003	2020-07-21T11:18:18	WHITE
1014	2020-08-06T09:45:53	MULTIPLE
1015		WHITE
1016	2020-08-10T12:44:43	WHITE
1017	2020-08-10T07:06:20	WHITE
1017	2020-08-11T07:06:20	WHITE
1017	2020-08-13T07:06:20	WHITE

and I want suppdm i.e.

SUBJID	IDVAR	IDVARVAL	QNAM	QLABEL	QVAL	QORIG
1014			RAC58	Asian	ASIAN	CRF
1014			RAC84	American Indian or Alaska Native	AMERICAN INDIAN OR ALASKA NATIVE	CRF
1017			RANDTC	Date/Time of Randomization	2020-08-10T12:44:43	eDT
1017			RANDTC	Date/Time of Randomization	2020-08-10T07:06:20	eDT
1017			RANDTC	Date/Time of Randomization	2020-08-11T07:06:20	eDT

This is only few example. Here in my DM data I have arount 400records. So kindly share easy way to get suppdm ( with ot without sas macro).

Thankyou so much in advance. Take care.

Regards

Priya

[Reeza]

You're showing data not in the original table. Where does that come from?
You need to provide more details about your transformation logic and ideally show what you've tried as well. Looks like a TRANSPOSE to me but hard to know. Where does QNAM come from? QORIG???

If you're using a specific SAS tool - SAS Clinical or something this isn't the right forum to post your question.

[pdhokriya]

QNAM and QORIG are the new variables which we need to create. Also QNAM and QORIG data details we need to add in programming.

[pdhokriya]

New varibales need to create by considering raw data

QNAM

QLABEL

QVAL

QORIG

[Reeza]

Ok. What is the transformation logic. Where does RAC58 come from based on the original data source? My guess is you have another look up table somewhere?

[pdhokriya]

Yes I presume correctly. Look this data , where I can see multiple race for 1 perticular subjid.

RACE	SUBJID	RACE_1	RACE_2	RACE_58	RACE_79	RACE_84	RANDTC
ASIAN	1001			ASIAN			2020-07-16T11:50:40
WHITE	1002	WHITE
WHITE	1003	WHITE					2020-07-21T11:18:18
BLACK OR AFRICAN AMERICAN	1004		BLACK OR AFRICAN AMERICAN
WHITE	1005	WHITE					2020-07-22T10:59:29
WHITE	1006	WHITE
WHITE	1007	WHITE					2020-07-31T12:22:53
WHITE	1008	WHITE					2020-08-03T12:06:53
WHITE	1009	WHITE					2020-08-03T13:26:28
WHITE	1010	WHITE					2020-08-03T14:34:01
WHITE	1011	WHITE					2020-08-04T09:38:42
WHITE	1012	WHITE					2020-08-04T10:14:21
WHITE	1013	WHITE					2020-08-05T11:42:10
MULTIPLE	1014	WHITE		ASIAN			2020-08-06T09:45:53
WHITE	1015	WHITE
WHITE	1016	WHITE					2020-08-10T12:44:43
WHITE	1017	WHITE					2020-08-10T07:06:20
WHITE	1019	WHITE
WHITE	1020	WHITE
WHITE	1021	WHITE
WHITE	1022	WHITE
WHITE	1023	WHITE
WHITE	1024	WHITE
WHITE	1026	WHITE

[andreas_lds]

And now please post the data in usable for so that we have something to work with.

[Reeza]

I think you need to start back at the beginning. Please show what you originally start out, including all information, and what you want as output. Ideally you also show what you tried so we know what approaches your considering and your skill level to provide an appropriate solution.