topic merging the data sets where all the columns having repeated data values in New SAS User

merging the data sets where all the columns having repeated data values

Sri_devi — Mon, 08 Aug 2022 11:09:15 GMT

Datasets1 :

Site_number

Subject_ID

Date_of_birth

Gender

Visit_name

Sample_not_taken

Sample_date

Sample_time

CATGY

Laboratory_test

Clin_sign

Specify_clin_signf

Was_repeat_sample_taken

10-006

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Hematology

Hematology -1

10-006

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

Serum chemistry -1

10-006

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

Thyroid assessment -1

10-006

25-Jan-91

D -1

31-Mar-22

13:24:00

Hematology

Hematology -1

10-006

25-Jan-91

D -1

31-Mar-22

13:24:00

Serum chemistry

Serum chemistry -1

10-006

25-Jan-91

D -1

31-Mar-22

13:24:00

Serum chemistry

Thyroid assessment -1

10-006

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Hematology

Hematology -1

Yes

10-006

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

Serum chemistry -1

10-006

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

Thyroid assessment -1

10-006

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Hematology

Hematology -1

10-006

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

Serum chemistry -1

10-006

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

Thyroid assessment -1

10-007

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Hematology

Hematology -1

10-007

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Serum chemistry

Serum chemistry -1

10-007

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Serum chemistry

Thyroid assessment -1

10-007

15-Jan-78

D -1

31-Mar-22

13:20:00

Hematology

Hematology -1

10-007

15-Jan-78

D -1

31-Mar-22

13:20:00

Serum chemistry

Serum chemistry -1

10-007

15-Jan-78

D -1

31-Mar-22

13:20:00

Serum chemistry

Thyroid assessment -1

10-007

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Hematology

Hematology -1

10-007

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

Serum chemistry -1

10-007

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

Thyroid assessment -1

10-007

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Hematology

Hematology -1

10-007

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

Serum chemistry -1

10-007

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

Thyroid assessment -1

10-014

19-Jul-97

SCREENING

5-Apr-22

17:09:00

Hematology

Hematology -1

10-014

19-Jul-97

SCREENING

5-Apr-22

17:09:00

Serum chemistry

Serum chemistry -1

10-014

19-Jul-97

SCREENING

5-Apr-22

17:09:00

Serum chemistry

Thyroid assessment -1

10-014

19-Jul-97

D -1

13-Apr-22

13:31:00

Hematology

Hematology -1

10-014

19-Jul-97

D -1

13-Apr-22

13:31:00

Serum chemistry

Serum chemistry -1

10-014

19-Jul-97

D -1

13-Apr-22

13:31:00

Serum chemistry

Thyroid assessment -1

10-014

19-Jul-97

D 1 POST 1HR

14-Apr-22

10:45:00

Hematology

Hematology -1

10-014

19-Jul-97

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

Serum chemistry -1

10-014

19-Jul-97

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

Thyroid assessment -1

10-014

19-Jul-97

D 7 EOS

21-Apr-22

15:05:00

Hematology

Hematology -1

10-014

19-Jul-97

D 7 EOS

21-Apr-22

15:05:00

Serum chemistry

Serum chemistry -1

10-014

19-Jul-97

D 7 EOS

21-Apr-22

15:05:00

Serum chemistry

Thyroid assessment -1

Data set 2 :

Vendor_Site_number	Subject_ID	Vendor_Date_of_birth	Vendor_Gender	Vendor_Visit_name	Vendor_Vis_Num	Vendor_Sample_date	Vendor_Sample_time	Vendor_CATGY	CAT	Vendor_Clin_sign
10	10-006	25-Jan-91	F	SCREENING	1	4-Mar-22	17:38:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-006	25-Jan-91	F	SCREENING	1	4-Mar-22	17:38:00	Hematology	HAEMATOLOGY	N/A
10	10-006	25-Jan-91	F	D -1	2	31-Mar-22	13:24:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-006	25-Jan-91	F	D -1	2	31-Mar-22	13:24:00	Hematology	HAEMATOLOGY	N/A
10	10-006	25-Jan-91	F	D 1 POST 1HR	3	1-Apr-22	11:19:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-006	25-Jan-91	F	D 1 POST 1HR	3	1-Apr-22	11:19:00	Hematology	HAEMATOLOGY	N/A
10	10-006	25-Jan-91	F	D 2	4	2-Apr-22	10:22:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-006	25-Jan-91	F	D 2	4	2-Apr-22	10:22:00	Hematology	HAEMATOLOGY	N/A
10	10-006	25-Jan-91	F	D 7 EOS	5	7-Apr-22	8:40:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-006	25-Jan-91	F	D 7 EOS	5	7-Apr-22	8:40:00	Hematology	HAEMATOLOGY	N/A
10	10-007	15-Jan-78	M	SCREENING	1	9-Mar-22	14:45:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-007	15-Jan-78	M	SCREENING	1	9-Mar-22	14:45:00	Hematology	HAEMATOLOGY	N/A
10	10-007	15-Jan-78	M	D -1	2	31-Mar-22	13:20:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-007	15-Jan-78	M	D -1	2	31-Mar-22	13:20:00	Hematology	HAEMATOLOGY	N/A
10	10-007	15-Jan-78	M	D 1 POST 1HR	3	1-Apr-22	12:22:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-007	15-Jan-78	M	D 1 POST 1HR	3	1-Apr-22	12:22:00	Hematology	HAEMATOLOGY	N/A
10	10-007	15-Jan-78	M	D 2	4	2-Apr-22	11:22:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-007	15-Jan-78	M	D 2	4	2-Apr-22	11:22:00	Hematology	HAEMATOLOGY	N/A
10	10-007	15-Jan-78	M	D 7 EOS	5	7-Apr-22	10:00:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-007	15-Jan-78	M	D 7 EOS	5	7-Apr-22	10:00:00	Hematology	HAEMATOLOGY	N/A
10	10-014	19-Jul-97	F	SCREENING	1	5-Apr-22	17:09:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-014	19-Jul-97	F	SCREENING	1	5-Apr-22	17:09:00	Hematology	HAEMATOLOGY	N/A
10	10-014	19-Jul-97	F	D -1	2	13-Apr-22	13:31:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-014	19-Jul-97	F	D -1	2	13-Apr-22	13:31:00	Hematology	HAEMATOLOGY	N/A
10	10-014	19-Jul-97	F	D 1 POST 1HR	3	14-Apr-22	10:45:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-014	19-Jul-97	F	D 1 POST 1HR	3	14-Apr-22	10:45:00	Hematology	HAEMATOLOGY	N/A
10	10-014	19-Jul-97	F	D 2	4	15-Apr-22	9:45:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-014	19-Jul-97	F	D 2	4	15-Apr-22	9:45:00	Hematology	HAEMATOLOGY	N/A
10	10-014	19-Jul-97	F	D 7 EOS	5	21-Apr-22	15:05:00	Serum chemistry	BIOCHEMISTRY	Normal
10	10-014	19-Jul-97	F	D 7 EOS	5	21-Apr-22	15:05:00	Hematology	HAEMATOLOGY	N/A

I want to merge this two datasets to generate a report to check the discrepancies between two and sort by date.

As the column data have repeated values i have used Proc SQL -Full join but i am not able to sort by date.Please anyone help me.

thanks!

Re: merging the data sets where all the columns having repeated data values

MarkusWeick — Mon, 08 Aug 2022 11:18:33 GMT

Hi @Sri_devi,

it looks as if your date columns are character values.

If so, you have to convert them into date values: For an example:

Solved: convert character date to YYMMDD10. - SAS Support Communities

Best Regards

Markus

Re: merging the data sets where all the columns having repeated data values

Sri_devi — Mon, 08 Aug 2022 11:29:10 GMT

Thanks, date column in both datasets are date format Date9.

Re: merging the data sets where all the columns having repeated data values

MarkusWeick — Mon, 08 Aug 2022 11:36:10 GMT

Have you tried proc sort?

Re: merging the data sets where all the columns having repeated data values

andreas_lds — Mon, 08 Aug 2022 11:40:10 GMT

As the column data have repeated values i have used Proc SQL -Full join but i am not able to sort by date.Please anyone help me.

Please post the code, the log and explain why do you think that sorting by date doesn't work.

Re: merging the data sets where all the columns having repeated data values

Sri_devi — Mon, 08 Aug 2022 11:47:57 GMT

In order to use proc sort there are repeated values in all column to use in by statement.

Re: merging the data sets where all the columns having repeated data values

Sri_devi — Mon, 08 Aug 2022 12:19:35 GMT

code :proc SQL;
create table vendor_crf1 as select a.Site_number,a.Subject_ID,a.Date_of_birth,a.Gender,a.Visit_name,a.Sample_not_taken,a.Sample_date,a.Sample_time,a.CATGY,a.Laboratory_test,a.Clin_sign,
b.Vendor_Site_number,b.Vendor_Subject_ID,b.Vendor_Date_of_birth,b.Vendor_Gender,b.Vendor_Visit_name,b.Vendor_Sample_date,b.Vendor_Sample_time,b.Vendor_CATGY,b.CAT,b.TEST,b.Vendor_Clin_sign
from CRF_Demog as a FULL JOIN external as b on
a.Subject_ID = b.Vendor_Subject_ID and
a.Visit_name = b.Vendor_Visit_name and
a.Sample_date = b.Vendor_Sample_date and
a.Sample_time = b.Vendor_Sample_time and
a.CATGY = b.Vendor_CATGY ;
quit;

output generated :

Site_number

Subject_ID

Date_of_birth

Gender

Visit_name

Sample_not_taken

Sample_date

Sample_time

CATGY

Laboratory_test

Clin_sign

Vendor_Site_number

Vendor_Date_of_birth

Vendor_Gender

Vendor_Visit_name

Vendor_Vis_Num

Vendor_Sample_date

Vendor_Sample_time

Vendor_CATGY

CAT

Vendor_Clin_sign

10-006

15-Jan-78

D -1

31-Mar-22

13:24:00

Hematology

Hematology -1

25-Jan-91

D -1

########

13:24:00

Hematology

HAEMATOLOGY

N/A

10-006

28-Nov-89

D -1

31-Mar-22

13:24:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D -1

########

13:24:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

25-Nov-94

D -1

31-Mar-22

13:24:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D -1

########

13:24:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

7-May-97

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

13-Nov-95

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

11-Feb-95

D 1 POST 1HR

1-Apr-22

11:19:00

Hematology

Hematology -1

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Hematology

HAEMATOLOGY

N/A

10-006

2-Dec-03

D 2

2-Apr-22

10:22:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D 2

2-Apr-22

10:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

17-Mar-97

D 2

2-Apr-22

10:22:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D 2

2-Apr-22

10:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

18-Sep-94

D 2

2-Apr-22

10:22:00

Hematology

Hematology -1

25-Jan-91

D 2

2-Apr-22

10:22:00

Hematology

HAEMATOLOGY

N/A

10-006

17-Jun-03

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

27-Jan-95

D 7 EOS

7-Apr-22

8:40:00

Hematology

Hematology -1

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Hematology

HAEMATOLOGY

N/A

10-006

3-Nov-02

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

15-Mar-76

SCREENING

4-Mar-22

17:38:00

Serum chemistry

Serum chemistry -1

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

18-May-89

SCREENING

4-Mar-22

17:38:00

Hematology

Hematology -1

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Hematology

HAEMATOLOGY

N/A

10-007

29-Apr-87

D -1

31-Mar-22

13:20:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D -1

########

13:20:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

11-Oct-03

D -1

31-Mar-22

13:20:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D -1

########

13:20:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

3-Jan-00

D -1

31-Mar-22

13:20:00

Hematology

Hematology -1

15-Jan-78

D -1

########

13:20:00

Hematology

HAEMATOLOGY

N/A

10-007

27-Mar-93

D 1 POST 1HR

1-Apr-22

12:22:00

Hematology

Hematology -1

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Hematology

HAEMATOLOGY

N/A

10-007

9-Jan-80

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

24-May-95

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

7-Jun-94

D 2

2-Apr-22

11:22:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D 2

2-Apr-22

11:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

26-Apr-96

D 2

2-Apr-22

11:22:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D 2

2-Apr-22

11:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

11-Sep-00

D 2

2-Apr-22

11:22:00

Hematology

Hematology -1

15-Jan-78

D 2

2-Apr-22

11:22:00

Hematology

HAEMATOLOGY

N/A

10-007

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

21-Mar-94

D 7 EOS

7-Apr-22

10:00:00

Hematology

Hematology -1

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Hematology

HAEMATOLOGY

N/A

10-007

5-Jul-96

SCREENING

9-Mar-22

14:45:00

Serum chemistry

Serum chemistry -1

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

17-Jul-96

SCREENING

9-Mar-22

14:45:00

Hematology

Hematology -1

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Hematology

HAEMATOLOGY

N/A

10-007

1-Feb-01

SCREENING

9-Mar-22

14:45:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D -1

13-Apr-22

13:31:00

Serum chemistry

Serum chemistry -1

19-Jul-97

D -1

########

13:31:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D -1

13-Apr-22

13:31:00

Serum chemistry

Thyroid assessment -1

19-Jul-97

D -1

########

13:31:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D -1

13-Apr-22

13:31:00

Hematology

Hematology -1

19-Jul-97

D -1

########

13:31:00

Hematology

HAEMATOLOGY

N/A

10-014

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

Thyroid assessment -1

19-Jul-97

D 1 POST 1HR

########

10:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

Serum chemistry -1

19-Jul-97

D 1 POST 1HR

########

10:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D 1 POST 1HR

14-Apr-22

10:45:00

Hematology

Hematology -1

19-Jul-97

D 1 POST 1HR

########

10:45:00

Hematology

HAEMATOLOGY

N/A

Output want :

different with above output is i want the the date columns(sample_date & vendor_sample_date) to be sorted as shown below.

Site_number

Subject_ID

Date_of_birth

Gender

Visit_name

Sample_not_taken

Sample_date

Sample_time

CATGY

Laboratory_test

Clin_sign

Vendor_Site_number

Vendor_Date_of_birth

Vendor_Gender

Vendor_Visit_name

Vendor_Vis_Num

Vendor_Sample_date

Vendor_Sample_time

Vendor_CATGY

CAT

Vendor_Clin_sign

10-006

15-Mar-76

SCREENING

4-Mar-22

17:38:00

Serum chemistry

Serum chemistry -1

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

18-May-89

SCREENING

4-Mar-22

17:38:00

Hematology

Hematology -1

25-Jan-91

SCREENING

4-Mar-22

17:38:00

Hematology

HAEMATOLOGY

N/A

10-007

5-Jul-96

SCREENING

9-Mar-22

14:45:00

Serum chemistry

Serum chemistry -1

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

17-Jul-96

SCREENING

9-Mar-22

14:45:00

Hematology

Hematology -1

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Hematology

HAEMATOLOGY

N/A

10-007

1-Feb-01

SCREENING

9-Mar-22

14:45:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

SCREENING

9-Mar-22

14:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

15-Jan-78

D -1

31-Mar-22

13:24:00

Hematology

Hematology -1

25-Jan-91

D -1

31-Mar-22

13:24:00

Hematology

HAEMATOLOGY

N/A

10-006

28-Nov-89

D -1

31-Mar-22

13:24:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D -1

31-Mar-22

13:24:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

25-Nov-94

D -1

31-Mar-22

13:24:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D -1

31-Mar-22

13:24:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

29-Apr-87

D -1

31-Mar-22

13:20:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D -1

31-Mar-22

13:20:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

11-Oct-03

D -1

31-Mar-22

13:20:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D -1

31-Mar-22

13:20:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

3-Jan-00

D -1

31-Mar-22

13:20:00

Hematology

Hematology -1

15-Jan-78

D -1

31-Mar-22

13:20:00

Hematology

HAEMATOLOGY

N/A

10-006

7-May-97

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

13-Nov-95

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

11-Feb-95

D 1 POST 1HR

1-Apr-22

11:19:00

Hematology

Hematology -1

25-Jan-91

D 1 POST 1HR

1-Apr-22

11:19:00

Hematology

HAEMATOLOGY

N/A

10-007

27-Mar-93

D 1 POST 1HR

1-Apr-22

12:22:00

Hematology

Hematology -1

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Hematology

HAEMATOLOGY

N/A

10-007

9-Jan-80

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

24-May-95

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D 1 POST 1HR

1-Apr-22

12:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

2-Dec-03

D 2

2-Apr-22

10:22:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D 2

2-Apr-22

10:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

17-Mar-97

D 2

2-Apr-22

10:22:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D 2

2-Apr-22

10:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

18-Sep-94

D 2

2-Apr-22

10:22:00

Hematology

Hematology -1

25-Jan-91

D 2

2-Apr-22

10:22:00

Hematology

HAEMATOLOGY

N/A

10-007

7-Jun-94

D 2

2-Apr-22

11:22:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D 2

2-Apr-22

11:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

26-Apr-96

D 2

2-Apr-22

11:22:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D 2

2-Apr-22

11:22:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

11-Sep-00

D 2

2-Apr-22

11:22:00

Hematology

Hematology -1

15-Jan-78

D 2

2-Apr-22

11:22:00

Hematology

HAEMATOLOGY

N/A

10-006

17-Jun-03

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

Thyroid assessment -1

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

BIOCHEMISTRY

Normal

10-006

27-Jan-95

D 7 EOS

7-Apr-22

8:40:00

Hematology

Hematology -1

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Hematology

HAEMATOLOGY

N/A

10-006

3-Nov-02

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

Serum chemistry -1

25-Jan-91

D 7 EOS

7-Apr-22

8:40:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

Serum chemistry -1

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

Thyroid assessment -1

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Serum chemistry

BIOCHEMISTRY

Normal

10-007

21-Mar-94

D 7 EOS

7-Apr-22

10:00:00

Hematology

Hematology -1

15-Jan-78

D 7 EOS

7-Apr-22

10:00:00

Hematology

HAEMATOLOGY

N/A

10-014

D -1

13-Apr-22

13:31:00

Serum chemistry

Serum chemistry -1

19-Jul-97

D -1

13-Apr-22

13:31:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D -1

13-Apr-22

13:31:00

Serum chemistry

Thyroid assessment -1

19-Jul-97

D -1

13-Apr-22

13:31:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D -1

13-Apr-22

13:31:00

Hematology

Hematology -1

19-Jul-97

D -1

13-Apr-22

13:31:00

Hematology

HAEMATOLOGY

N/A

10-014

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

Thyroid assessment -1

19-Jul-97

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

Serum chemistry -1

19-Jul-97

D 1 POST 1HR

14-Apr-22

10:45:00

Serum chemistry

BIOCHEMISTRY

Normal

10-014

D 1 POST 1HR

14-Apr-22

10:45:00

Hematology

Hematology -1

19-Jul-97

D 1 POST 1HR

14-Apr-22

10:45:00

Hematology

HAEMATOLOGY

N/A

Re: merging the data sets where all the columns having repeated data values

Kurt_Bremser — Mon, 08 Aug 2022 13:14:54 GMT

If you want a specific sort order from SQL, you must tell it so:

proc sql;
create table vendor_crf1 as
  select
    a.Site_number,a.Subject_ID,a.Date_of_birth,a.Gender,a.Visit_name,a.Sample_not_taken,a.Sample_date,a.Sample_time,a.CATGY,a.Laboratory_test,a.Clin_sign,
    b.Vendor_Site_number,b.Vendor_Subject_ID,b.Vendor_Date_of_birth,b.Vendor_Gender,b.Vendor_Visit_name,b.Vendor_Sample_date,b.Vendor_Sample_time,b.Vendor_CATGY,b.CAT,b.TEST,b.Vendor_Clin_sign,
    coalesce(a.sample_date,b.vendor_sample_date) as s_date, coalesce(a.sample_time,b.vendor_sample_time) as s_time
  from CRF_Demog as a FULL JOIN external as b on
    a.Subject_ID = b.Vendor_Subject_ID and
    a.Visit_name = b.Vendor_Visit_name and
    a.Sample_date = b.Vendor_Sample_date and
    a.Sample_time = b.Vendor_Sample_time and
    a.CATGY = b.Vendor_CATGY
  order by
    s_date, s_time
;
quit;

The COALESCEd variables are necessary because of the FULL JOIN.

Re: merging the data sets where all the columns having repeated data values

Sri_devi — Tue, 09 Aug 2022 02:29:52 GMT

Thank you! it worked.