cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

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

Posted 08-08-2022 07:09 AM (1028 views)

Datasets1 :

Site_numberSubject_IDDate_of_birthGenderVisit_nameSample_not_takenSample_dateSample_timeCATGYLaboratory_testClin_signSpecify_clin_signfWas_repeat_sample_taken
1010-00625-Jan-91FSCREENING 4-Mar-2217:38:00HematologyHematology -1No No
1010-00625-Jan-91FSCREENING 4-Mar-2217:38:00Serum chemistrySerum chemistry -1No No
1010-00625-Jan-91FSCREENING 4-Mar-2217:38:00Serum chemistryThyroid assessment -1No No
1010-00625-Jan-91FD -1 31-Mar-2213:24:00HematologyHematology -1No No
1010-00625-Jan-91FD -1 31-Mar-2213:24:00Serum chemistrySerum chemistry -1No No
1010-00625-Jan-91FD -1 31-Mar-2213:24:00Serum chemistryThyroid assessment -1No No
1010-00625-Jan-91FD 1 POST 1HR 1-Apr-2211:19:00HematologyHematology -1No Yes
1010-00625-Jan-91FD 1 POST 1HR 1-Apr-2211:19:00Serum chemistrySerum chemistry -1No No
1010-00625-Jan-91FD 1 POST 1HR 1-Apr-2211:19:00Serum chemistryThyroid assessment -1No No
1010-00625-Jan-91FD 7 EOS 7-Apr-228:40:00HematologyHematology -1No No
1010-00625-Jan-91FD 7 EOS 7-Apr-228:40:00Serum chemistrySerum chemistry -1No No
1010-00625-Jan-91FD 7 EOS 7-Apr-228:40:00Serum chemistryThyroid assessment -1No No
1010-00715-Jan-78MSCREENING 9-Mar-2214:45:00HematologyHematology -1No No
1010-00715-Jan-78MSCREENING 9-Mar-2214:45:00Serum chemistrySerum chemistry -1No No
1010-00715-Jan-78MSCREENING 9-Mar-2214:45:00Serum chemistryThyroid assessment -1No No
1010-00715-Jan-78MD -1 31-Mar-2213:20:00HematologyHematology -1No No
1010-00715-Jan-78MD -1 31-Mar-2213:20:00Serum chemistrySerum chemistry -1No No
1010-00715-Jan-78MD -1 31-Mar-2213:20:00Serum chemistryThyroid assessment -1No No
1010-00715-Jan-78MD 1 POST 1HR 1-Apr-2212:22:00HematologyHematology -1No No
1010-00715-Jan-78MD 1 POST 1HR 1-Apr-2212:22:00Serum chemistrySerum chemistry -1No No
1010-00715-Jan-78MD 1 POST 1HR 1-Apr-2212:22:00Serum chemistryThyroid assessment -1No No
1010-00715-Jan-78MD 7 EOS 7-Apr-2210:00:00HematologyHematology -1No No
1010-00715-Jan-78MD 7 EOS 7-Apr-2210:00:00Serum chemistrySerum chemistry -1No No
1010-00715-Jan-78MD 7 EOS 7-Apr-2210:00:00Serum chemistryThyroid assessment -1No No
1010-01419-Jul-97FSCREENING 5-Apr-2217:09:00HematologyHematology -1No No
1010-01419-Jul-97FSCREENING 5-Apr-2217:09:00Serum chemistrySerum chemistry -1No No
1010-01419-Jul-97FSCREENING 5-Apr-2217:09:00Serum chemistryThyroid assessment -1No No
1010-01419-Jul-97FD -1 13-Apr-2213:31:00HematologyHematology -1No No
1010-01419-Jul-97FD -1 13-Apr-2213:31:00Serum chemistrySerum chemistry -1No No
1010-01419-Jul-97FD -1 13-Apr-2213:31:00Serum chemistryThyroid assessment -1No No
1010-01419-Jul-97FD 1 POST 1HR 14-Apr-2210:45:00HematologyHematology -1No No
1010-01419-Jul-97FD 1 POST 1HR 14-Apr-2210:45:00Serum chemistrySerum chemistry -1No No
1010-01419-Jul-97FD 1 POST 1HR 14-Apr-2210:45:00Serum chemistryThyroid assessment -1No No
1010-01419-Jul-97FD 7 EOS 21-Apr-2215:05:00HematologyHematology -1No No
1010-01419-Jul-97FD 7 EOS 21-Apr-2215:05:00Serum chemistrySerum chemistry -1No No
1010-01419-Jul-97FD 7 EOS 21-Apr-2215:05:00Serum chemistryThyroid assessment -1No No

 

 

Data set 2 :

Vendor_Site_numberSubject_IDVendor_Date_of_birthVendor_GenderVendor_Visit_nameVendor_Vis_NumVendor_Sample_dateVendor_Sample_timeVendor_CATGYCATVendor_Clin_sign
1010-00625-Jan-91FSCREENING14-Mar-2217:38:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Jan-91FSCREENING14-Mar-2217:38:00HematologyHAEMATOLOGYN/A
1010-00625-Jan-91FD -1231-Mar-2213:24:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Jan-91FD -1231-Mar-2213:24:00HematologyHAEMATOLOGYN/A
1010-00625-Jan-91FD 1 POST 1HR31-Apr-2211:19:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Jan-91FD 1 POST 1HR31-Apr-2211:19:00HematologyHAEMATOLOGYN/A
1010-00625-Jan-91FD 242-Apr-2210:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Jan-91FD 242-Apr-2210:22:00HematologyHAEMATOLOGYN/A
1010-00625-Jan-91FD 7 EOS57-Apr-228:40:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Jan-91FD 7 EOS57-Apr-228:40:00HematologyHAEMATOLOGYN/A
1010-00715-Jan-78MSCREENING19-Mar-2214:45:00Serum chemistryBIOCHEMISTRYNormal
1010-00715-Jan-78MSCREENING19-Mar-2214:45:00HematologyHAEMATOLOGYN/A
1010-00715-Jan-78MD -1231-Mar-2213:20:00Serum chemistryBIOCHEMISTRYNormal
1010-00715-Jan-78MD -1231-Mar-2213:20:00HematologyHAEMATOLOGYN/A
1010-00715-Jan-78MD 1 POST 1HR31-Apr-2212:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00715-Jan-78MD 1 POST 1HR31-Apr-2212:22:00HematologyHAEMATOLOGYN/A
1010-00715-Jan-78MD 242-Apr-2211:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00715-Jan-78MD 242-Apr-2211:22:00HematologyHAEMATOLOGYN/A
1010-00715-Jan-78MD 7 EOS57-Apr-2210:00:00Serum chemistryBIOCHEMISTRYNormal
1010-00715-Jan-78MD 7 EOS57-Apr-2210:00:00HematologyHAEMATOLOGYN/A
1010-01419-Jul-97FSCREENING15-Apr-2217:09:00Serum chemistryBIOCHEMISTRYNormal
1010-01419-Jul-97FSCREENING15-Apr-2217:09:00HematologyHAEMATOLOGYN/A
1010-01419-Jul-97FD -1213-Apr-2213:31:00Serum chemistryBIOCHEMISTRYNormal
1010-01419-Jul-97FD -1213-Apr-2213:31:00HematologyHAEMATOLOGYN/A
1010-01419-Jul-97FD 1 POST 1HR314-Apr-2210:45:00Serum chemistryBIOCHEMISTRYNormal
1010-01419-Jul-97FD 1 POST 1HR314-Apr-2210:45:00HematologyHAEMATOLOGYN/A
1010-01419-Jul-97FD 2415-Apr-229:45:00Serum chemistryBIOCHEMISTRYNormal
1010-01419-Jul-97FD 2415-Apr-229:45:00HematologyHAEMATOLOGYN/A
1010-01419-Jul-97FD 7 EOS521-Apr-2215:05:00Serum chemistryBIOCHEMISTRYNormal
1010-01419-Jul-97FD 7 EOS521-Apr-2215:05:00HematologyHAEMATOLOGYN/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!

 

 

0 Likes
Reply
8 REPLIES 8
MarkusWeick
Barite | Level 11 MarkusWeick
Barite | Level 11

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

Posted 08-08-2022 07:18 AM (1011 views)  |   In reply to Sri_devi

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

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Reply
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

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

Posted 08-08-2022 07:29 AM (1006 views)  |   In reply to MarkusWeick
Thanks, date column in both datasets are date format Date9.
0 Likes
Reply
MarkusWeick
Barite | Level 11 MarkusWeick
Barite | Level 11

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

Posted 08-08-2022 07:36 AM (1001 views)  |   In reply to Sri_devi

Have you tried proc sort?

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
0 Likes
Reply
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

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

Posted 08-08-2022 07:47 AM (993 views)  |   In reply to MarkusWeick
In order to use proc sort there are repeated values in all column to use in by statement.
0 Likes
Reply
andreas_lds
Jade | Level 19 andreas_lds
Jade | Level 19

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

Posted 08-08-2022 07:40 AM (999 views)  |   In reply to Sri_devi

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.

0 Likes
Reply
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

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

Posted 08-08-2022 08:19 AM (979 views)  |   In reply to andreas_lds

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_numberSubject_IDDate_of_birthGenderVisit_nameSample_not_takenSample_dateSample_timeCATGYLaboratory_testClin_signVendor_Site_numberVendor_Date_of_birthVendor_GenderVendor_Visit_nameVendor_Vis_NumVendor_Sample_dateVendor_Sample_timeVendor_CATGYCATVendor_Clin_sign
1010-00615-Jan-78MD -1 31-Mar-2213:24:00HematologyHematology -1No1025-Jan-91FD -12########13:24:00HematologyHAEMATOLOGYN/A
1010-00628-Nov-89FD -1 31-Mar-2213:24:00Serum chemistrySerum chemistry -1No1025-Jan-91FD -12########13:24:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Nov-94MD -1 31-Mar-2213:24:00Serum chemistryThyroid assessment -1No1025-Jan-91FD -12########13:24:00Serum chemistryBIOCHEMISTRYNormal
1010-0067-May-97MD 1 POST 1HR1-Apr-2211:19:00Serum chemistrySerum chemistry -1No1025-Jan-91FD 1 POST 1HR31-Apr-2211:19:00Serum chemistryBIOCHEMISTRYNormal
1010-00613-Nov-95MD 1 POST 1HR1-Apr-2211:19:00Serum chemistryThyroid assessment -1No1025-Jan-91FD 1 POST 1HR31-Apr-2211:19:00Serum chemistryBIOCHEMISTRYNormal
1010-00611-Feb-95FD 1 POST 1HR1-Apr-2211:19:00HematologyHematology -1No1025-Jan-91FD 1 POST 1HR31-Apr-2211:19:00HematologyHAEMATOLOGYN/A
1010-0062-Dec-03FD 2 2-Apr-2210:22:00Serum chemistryThyroid assessment -1No1025-Jan-91FD 242-Apr-2210:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00617-Mar-97FD 2 2-Apr-2210:22:00Serum chemistrySerum chemistry -1No1025-Jan-91FD 242-Apr-2210:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00618-Sep-94FD 2 2-Apr-2210:22:00HematologyHematology -1No1025-Jan-91FD 242-Apr-2210:22:00HematologyHAEMATOLOGYN/A
1010-00617-Jun-03MD 7 EOS 7-Apr-228:40:00Serum chemistryThyroid assessment -1No1025-Jan-91FD 7 EOS57-Apr-228:40:00Serum chemistryBIOCHEMISTRYNormal
1010-00627-Jan-95MD 7 EOS 7-Apr-228:40:00HematologyHematology -1No1025-Jan-91FD 7 EOS57-Apr-228:40:00HematologyHAEMATOLOGYN/A
1010-0063-Nov-02MD 7 EOS 7-Apr-228:40:00Serum chemistrySerum chemistry -1No1025-Jan-91FD 7 EOS57-Apr-228:40:00Serum chemistryBIOCHEMISTRYNormal
1010-00615-Mar-76MSCREENING 4-Mar-2217:38:00Serum chemistrySerum chemistry -1No1025-Jan-91FSCREENING14-Mar-2217:38:00Serum chemistryBIOCHEMISTRYNormal
1010-00625-Jan-91FSCREENING 4-Mar-2217:38:00Serum chemistryThyroid assessment -1No1025-Jan-91FSCREENING14-Mar-2217:38:00Serum chemistryBIOCHEMISTRYNormal
1010-00618-May-89FSCREENING 4-Mar-2217:38:00HematologyHematology -1No1025-Jan-91FSCREENING14-Mar-2217:38:00HematologyHAEMATOLOGYN/A
1010-00729-Apr-87MD -1 31-Mar-2213:20:00Serum chemistrySerum chemistry -1No1015-Jan-78MD -12########13:20:00Serum chemistryBIOCHEMISTRYNormal
1010-00711-Oct-03MD -1 31-Mar-2213:20:00Serum chemistryThyroid assessment -1No1015-Jan-78MD -12########13:20:00Serum chemistryBIOCHEMISTRYNormal
1010-0073-Jan-00FD -1 31-Mar-2213:20:00HematologyHematology -1No1015-Jan-78MD -12########13:20:00HematologyHAEMATOLOGYN/A
1010-00727-Mar-93MD 1 POST 1HR1-Apr-2212:22:00HematologyHematology -1No1015-Jan-78MD 1 POST 1HR31-Apr-2212:22:00HematologyHAEMATOLOGYN/A
1010-0079-Jan-80FD 1 POST 1HR1-Apr-2212:22:00Serum chemistryThyroid assessment -1No1015-Jan-78MD 1 POST 1HR31-Apr-2212:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00724-May-95FD 1 POST 1HR1-Apr-2212:22:00Serum chemistrySerum chemistry -1No1015-Jan-78MD 1 POST 1HR31-Apr-2212:22:00Serum chemistryBIOCHEMISTRYNormal
1010-0077-Jun-94MD 2 2-Apr-2211:22:00Serum chemistrySerum chemistry -1No1015-Jan-78MD 242-Apr-2211:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00726-Apr-96MD 2 2-Apr-2211:22:00Serum chemistryThyroid assessment -1No1015-Jan-78MD 242-Apr-2211:22:00Serum chemistryBIOCHEMISTRYNormal
1010-00711-Sep-00FD 2 2-Apr-2211:22:00HematologyHematology -1No1015-Jan-78MD 242-Apr-2211:22:00HematologyHAEMATOLOGYN/A
1010-007  D 7 EOS 7-Apr-2210:00:00Serum chemistrySerum chemistry -1No1015-Jan-78MD 7 EOS57-Apr-2210:00:00Serum chemistryBIOCHEMISTRYNormal
1010-007  D 7 EOS 7-Apr-2210:00:00Serum chemistryThyroid assessment -1No1015-Jan-78MD 7 EOS57-Apr-2210:00:00Serum chemistryBIOCHEMISTRYNormal
1010-00721-Mar-94MD 7 EOS 7-Apr-2210:00:00HematologyHematology -1No1015-Jan-78MD 7 EOS57-Apr-2210:00:00HematologyHAEMATOLOGYN/A
1010-0075-Jul-96MSCREENING 9-Mar-2214:45:00Serum chemistrySerum chemistry -1No1015-Jan-78MSCREENING19-Mar-2214:45:00Serum chemistryBIOCHEMISTRYNormal
1010-00717-Jul-96FSCREENING 9-Mar-2214:45:00HematologyHematology -1No1015-Jan-78MSCREENING19-Mar-2214:45:00HematologyHAEMATOLOGYN/A
1010-0071-Feb-01MSCREENING 9-Mar-2214:45:00Serum chemistryThyroid assessment -1No1015-Jan-78MSCREENING19-Mar-2214:45:00Serum chemistryBIOCHEMISTRYNormal
1010-014  D -1 13-Apr-2213:31:00Serum chemistrySerum chemistry -1No1019-Jul-97FD -12########13:31:00Serum chemistryBIOCHEMISTRYNormal
1010-014  D -1 13-Apr-2213:31:00Serum chemistryThyroid assessment -1No1019-Jul-97FD -12########13:31:00Serum chemistryBIOCHEMISTRYNormal
1010-014  D -1 13-Apr-2213:31:00HematologyHematology -1No1019-Jul-97FD -12########13:31:00HematologyHAEMATOLOGYN/A
1010-014  D 1 POST 1HR14-Apr-2210:45:00Serum chemistryThyroid assessment -1No1019-Jul-97FD 1 POST 1HR3########10:45:00Serum chemistryBIOCHEMISTRYNormal
1010-014  D 1 POST 1HR14-Apr-2210:45:00Serum chemistrySerum chemistry -1No1019-Jul-97FD 1 POST 1HR3########10:45:00Serum chemistryBIOCHEMISTRYNormal
1010-014  D 1 POST 1HR14-Apr-2210:45:00HematologyHematology -1No1019-Jul-97FD 1 POST 1HR3########10:45:00HematologyHAEMATOLOGYN/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_numberSubject_IDDate_of_birthGenderVisit_nameSample_not_takenSample_dateSample_timeCATGYLaboratory_testClin_signVendor_Site_numberVendor_Date_of_birthVendor_GenderVendor_Visit_nameVendor_Vis_NumVendor_Sample_dateVendor_Sample_timeVendor_CATGYCATVendor_Clin_sign
1010-00615-Mar-76MSCREENING 4-Mar-2217:38:00Serum chemistrySerum chemistry -1No1025-Jan-91FSCREENING14-Mar-2217:38:00Serum chemistryBIOCHEMISTRYNormal 
1010-00625-Jan-91FSCREENING 4-Mar-2217:38:00Serum chemistryThyroid assessment -1No1025-Jan-91FSCREENING14-Mar-2217:38:00Serum chemistryBIOCHEMISTRYNormal 
1010-00618-May-89FSCREENING 4-Mar-2217:38:00HematologyHematology -1No1025-Jan-91FSCREENING14-Mar-2217:38:00HematologyHAEMATOLOGYN/A 
1010-0075-Jul-96MSCREENING 9-Mar-2214:45:00Serum chemistrySerum chemistry -1No1015-Jan-78MSCREENING19-Mar-2214:45:00Serum chemistryBIOCHEMISTRYNormal 
1010-00717-Jul-96FSCREENING 9-Mar-2214:45:00HematologyHematology -1No1015-Jan-78MSCREENING19-Mar-2214:45:00HematologyHAEMATOLOGYN/A 
1010-0071-Feb-01MSCREENING 9-Mar-2214:45:00Serum chemistryThyroid assessment -1No1015-Jan-78MSCREENING19-Mar-2214:45:00Serum chemistryBIOCHEMISTRYNormal 
1010-00615-Jan-78MD -1 31-Mar-2213:24:00HematologyHematology -1No1025-Jan-91FD -1231-Mar-2213:24:00HematologyHAEMATOLOGYN/A 
1010-00628-Nov-89FD -1 31-Mar-2213:24:00Serum chemistrySerum chemistry -1No1025-Jan-91FD -1231-Mar-2213:24:00Serum chemistryBIOCHEMISTRYNormal 
1010-00625-Nov-94MD -1 31-Mar-2213:24:00Serum chemistryThyroid assessment -1No1025-Jan-91FD -1231-Mar-2213:24:00Serum chemistryBIOCHEMISTRYNormal 
1010-00729-Apr-87MD -1 31-Mar-2213:20:00Serum chemistrySerum chemistry -1No1015-Jan-78MD -1231-Mar-2213:20:00Serum chemistryBIOCHEMISTRYNormal 
1010-00711-Oct-03MD -1 31-Mar-2213:20:00Serum chemistryThyroid assessment -1No1015-Jan-78MD -1231-Mar-2213:20:00Serum chemistryBIOCHEMISTRYNormal 
1010-0073-Jan-00FD -1 31-Mar-2213:20:00HematologyHematology -1No1015-Jan-78MD -1231-Mar-2213:20:00HematologyHAEMATOLOGYN/A 
1010-0067-May-97MD 1 POST 1HR1-Apr-2211:19:00Serum chemistrySerum chemistry -1No1025-Jan-91FD 1 POST 1HR31-Apr-2211:19:00Serum chemistryBIOCHEMISTRYNormal 
1010-00613-Nov-95MD 1 POST 1HR1-Apr-2211:19:00Serum chemistryThyroid assessment -1No1025-Jan-91FD 1 POST 1HR31-Apr-2211:19:00Serum chemistryBIOCHEMISTRYNormal 
1010-00611-Feb-95FD 1 POST 1HR1-Apr-2211:19:00HematologyHematology -1No1025-Jan-91FD 1 POST 1HR31-Apr-2211:19:00HematologyHAEMATOLOGYN/A 
1010-00727-Mar-93MD 1 POST 1HR1-Apr-2212:22:00HematologyHematology -1No1015-Jan-78MD 1 POST 1HR31-Apr-2212:22:00HematologyHAEMATOLOGYN/A 
1010-0079-Jan-80FD 1 POST 1HR1-Apr-2212:22:00Serum chemistryThyroid assessment -1No1015-Jan-78MD 1 POST 1HR31-Apr-2212:22:00Serum chemistryBIOCHEMISTRYNormal 
1010-00724-May-95FD 1 POST 1HR1-Apr-2212:22:00Serum chemistrySerum chemistry -1No1015-Jan-78MD 1 POST 1HR31-Apr-2212:22:00Serum chemistryBIOCHEMISTRYNormal 
1010-0062-Dec-03FD 2 2-Apr-2210:22:00Serum chemistryThyroid assessment -1No1025-Jan-91FD 242-Apr-2210:22:00Serum chemistryBIOCHEMISTRYNormal 
1010-00617-Mar-97FD 2 2-Apr-2210:22:00Serum chemistrySerum chemistry -1No1025-Jan-91FD 242-Apr-2210:22:00Serum chemistryBIOCHEMISTRYNormal 
1010-00618-Sep-94FD 2 2-Apr-2210:22:00HematologyHematology -1No1025-Jan-91FD 242-Apr-2210:22:00HematologyHAEMATOLOGYN/A 
1010-0077-Jun-94MD 2 2-Apr-2211:22:00Serum chemistrySerum chemistry -1No1015-Jan-78MD 242-Apr-2211:22:00Serum chemistryBIOCHEMISTRYNormal 
1010-00726-Apr-96MD 2 2-Apr-2211:22:00Serum chemistryThyroid assessment -1No1015-Jan-78MD 242-Apr-2211:22:00Serum chemistryBIOCHEMISTRYNormal 
1010-00711-Sep-00FD 2 2-Apr-2211:22:00HematologyHematology -1No1015-Jan-78MD 242-Apr-2211:22:00HematologyHAEMATOLOGYN/A 
1010-00617-Jun-03MD 7 EOS 7-Apr-228:40:00Serum chemistryThyroid assessment -1No1025-Jan-91FD 7 EOS57-Apr-228:40:00Serum chemistryBIOCHEMISTRYNormal 
1010-00627-Jan-95MD 7 EOS 7-Apr-228:40:00HematologyHematology -1No1025-Jan-91FD 7 EOS57-Apr-228:40:00HematologyHAEMATOLOGYN/A 
1010-0063-Nov-02MD 7 EOS 7-Apr-228:40:00Serum chemistrySerum chemistry -1No1025-Jan-91FD 7 EOS57-Apr-228:40:00Serum chemistryBIOCHEMISTRYNormal 
1010-007  D 7 EOS 7-Apr-2210:00:00Serum chemistrySerum chemistry -1No1015-Jan-78MD 7 EOS57-Apr-2210:00:00Serum chemistryBIOCHEMISTRYNormal 
1010-007  D 7 EOS 7-Apr-2210:00:00Serum chemistryThyroid assessment -1No1015-Jan-78MD 7 EOS57-Apr-2210:00:00Serum chemistryBIOCHEMISTRYNormal 
1010-00721-Mar-94MD 7 EOS 7-Apr-2210:00:00HematologyHematology -1No1015-Jan-78MD 7 EOS57-Apr-2210:00:00HematologyHAEMATOLOGYN/A 
1010-014  D -1 13-Apr-2213:31:00Serum chemistrySerum chemistry -1No1019-Jul-97FD -1213-Apr-2213:31:00Serum chemistryBIOCHEMISTRYNormal 
1010-014  D -1 13-Apr-2213:31:00Serum chemistryThyroid assessment -1No1019-Jul-97FD -1213-Apr-2213:31:00Serum chemistryBIOCHEMISTRYNormal 
1010-014  D -1 13-Apr-2213:31:00HematologyHematology -1No1019-Jul-97FD -1213-Apr-2213:31:00HematologyHAEMATOLOGYN/A 
1010-014  D 1 POST 1HR14-Apr-2210:45:00Serum chemistryThyroid assessment -1No1019-Jul-97FD 1 POST 1HR314-Apr-2210:45:00Serum chemistryBIOCHEMISTRYNormal 
1010-014  D 1 POST 1HR14-Apr-2210:45:00Serum chemistrySerum chemistry -1No1019-Jul-97FD 1 POST 1HR314-Apr-2210:45:00Serum chemistryBIOCHEMISTRYNormal 
1010-014  D 1 POST 1HR14-Apr-2210:45:00HematologyHematology -1No1019-Jul-97FD 1 POST 1HR314-Apr-2210:45:00HematologyHAEMATOLOGYN/A 

 

0 Likes
Reply
Kurt_Bremser
Super User Kurt_Bremser
Super User

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

Posted 08-08-2022 09:14 AM (968 views)  |   In reply to Sri_devi

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX
Reply

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Watch Mastering the WHERE Clause in PROC SQL on YouTube

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • ‎08-08-2022 07:09 AM
  • 1029 views
  • 2 likes
  • 4 in conversation