topic Re: matching data and non matching data by sql in SAS Programming

matching data and non matching data by sql

thanikondharish — Fri, 27 Jul 2018 03:55:49 GMT

Data Ae;
Input Subjid $ Visit $ Aeterm $;
Cards;
U001 Week1 Headac
U002 Week1 Rashs
U003 Week1 Fever
U001 Week2 Vomiting
U002 Week2 Skinprb
U003 Week2 Rashs
U001 Week3 Headac
U002 Week3 cold
U003 Week3 Fever
;

by using above data to form the matching data and non matching two separate datasets by using sql

Re: matching data and non matching data by sql

ChrisNZ — Fri, 27 Jul 2018 04:05:35 GMT

You might be able to use fewer words if you really tried, but it would be hard... 😕

Please give more information. For example the expected output.

Re: matching data and non matching data by sql

thanikondharish — Fri, 27 Jul 2018 04:08:42 GMT

U001 Week3 Asp-05mg
U002 Week3 Placebo
U003 Week3 Asp-15mg
u003 week4 asprin
u004 week1 drug
;

Data Ae;
Input Subjid $ Visit $ Aeterm $;
Cards;
U001 Week1 Headac
U002 Week1 Rashs
U003 Week1 Fever
U001 Week2 Vomiting
U002 Week2 Skinprb
U003 Week2 Rashs
U001 Week3 Headac
U003 week6 pain
u005 week1 fever
;

some record not matched by subjid and visit wise so i want matching data
and non matching data by using sql

Re: matching data and non matching data by sql

PGStats — Fri, 27 Jul 2018 04:30:59 GMT

I would do something like


proc sql;
create table matching as
select Mh.*, Ae.Aeterm
from Mh inner join Ae
    on Mh.subjid=Ae.subjid and Mh.visit=Ae.visit;
create table nonMatching as
select subjid, visit, catx(" - ", "Dose", dose) as value
from Mh
where not exists (select * from Ae where Mh.subjid=Ae.subjid and Mh.visit=Ae.visit)
union all
select subjid, visit, catx(" - ", "Aeterm", Aeterm) as value
from Ae
where not exists (select * from Mh where Mh.subjid=Ae.subjid and Mh.visit=Ae.visit);
quit;

Re: matching data and non matching data by sql

thanikondharish — Fri, 27 Jul 2018 04:36:42 GMT

data lb ;
input subjid $ visit $ test $ ;
cards ;
u101 week1 rbc
u101 week2 wbc
u102 week1 rbc
u103 week2 plasma
;

if we add this data also how will we do matching data and non matching data.
because it is easy for me to solve the two datasets but somewhat hard for
me if any other dataset added

Re: matching data and non matching data by sql

PGStats — Fri, 27 Jul 2018 04:43:59 GMT

Please state the problem clearly, to its full extent.

Re: matching data and non matching data by sql

thanikondharish — Fri, 27 Jul 2018 04:48:42 GMT

Data Mh;

Input Subjid $ Visit $ Dose $;

Cards;

U001 Week1 Asp-05mg

U002 Week1 Asp-10mg

U003 Week1 Asp-15mg

U001 Week2 Asp-05mg

U002 Week2 Asp-10mg

U003 Week2 Placebo

U001 Week3 Asp-05mg

U002 Week3 Placebo

U003 Week3 Asp-15mg

;

Data Ae;

Input Subjid $ Visit $ Aeterm $;

Cards;

U001 Week1 Headac

U002 Week1 Rashs

U003 Week1 Fever

U001 Week2 Vomiting

U002 Week2 Skinprb

U003 Week2 Rashs

U001 Week3 Headac

U002 Week3 cold

U003 Week3 Fever

;
data lb ;
input subjid $ visit $ test $ ;
cards ;

U001 Week1 RBC

U002 Week1 WBC

U003 Week1 PLASMA

U001 Week2 ELISA

U002 Week2 WBC

U003 Week2 RBC

;

How to get matching and non matching data by using sql

Re: matching data and non matching data by sql

andreas_lds — Fri, 27 Jul 2018 05:26:49 GMT

What was wrong with the code @PGStats posted? From text and dataset-names it is hardly possible to understand what you have and what you expect as result.

Re: matching data and non matching data by sql

thanikondharish — Fri, 27 Jul 2018 05:32:43 GMT

code is good in this we used only two data sets but i gave one more dataset
total three datasets how to solve matching and non matching data

Re: matching data and non matching data by sql

ballardw — Fri, 27 Jul 2018 18:00:38 GMT

@thanikondharish wrote:
code is good in this we used only two data sets but i gave one more dataset
total three datasets how to solve matching and non matching data

WHICH match? With 3 data sets you have possibilities of

Match in 1 and 2

Match in 1 and 3

Match in 2 and 3

Match in all of 1, 2, and 3

And some similar combinations for not matching.

So, what is the actual rule for "matching" in more than one data set? Note that if you go to 4 the number of possibilities goes up.

The phrase "2 to the nth power" is involved here.

Re: matching data and non matching data by sql

thanikondharish — Fri, 27 Jul 2018 18:15:42 GMT

Match in all of 1,2and3

Re: matching data and non matching data by sql

PGStats — Fri, 27 Jul 2018 21:56:35 GMT

May I suggest the following approach which is simpler to implement for any number of datasets:

Data Mh;
Input Subjid $ Visit $ Dose $;
Cards;
U001 Week1 Asp-05mg
U002 Week1 Asp-10mg
U003 Week1 Asp-15mg
U001 Week2 Asp-05mg
U002 Week2 Asp-10mg
U003 Week2 Placebo
U001 Week3 Asp-05mg
U002 Week3 Placebo
U003 Week3 Asp-15mg
U004 Week1 Asp-05mg
;

Data Ae;
Input Subjid $ Visit $ Aeterm $;
Cards;
U001 Week1 Headac
U002 Week1 Rashs
U003 Week1 Fever
U001 Week2 Vomiting
U002 Week2 Skinprb
U003 Week2 Rashs
U001 Week3 Headac
U002 Week3 cold
U003 Week3 Fever
U004 Week2 Dizzy
;

data lb ;
input subjid $ visit $ test $ ;
cards ;
U001 Week1 RBC
U002 Week1 WBC
U003 Week1 PLASMA
U001 Week2 ELISA
U002 Week2 WBC
U003 Week2 RBC
U004 Week3 ABC
;

proc sql;
create table matching as
select 
    coalesce(mh.subjid, ae.subjid, lb.subjid) as subjid,
    coalesce(mh.visit, ae.visit, lb.visit) as visit,
    mh.dose, Ae.Aeterm, lb.test,
    cmiss(mh.dose, ae.aeterm, lb.test) as missMatch
from 
    mh full join 
    ae on Mh.subjid=Ae.subjid and Mh.visit=Ae.visit full join
    lb on Mh.subjid=lb.subjid and Mh.visit=lb.visit
order by missMatch, subjid, visit;
select * from matching;
quit;

                  subjid    visit     Dose      Aeterm    test      missMatch
                  -----------------------------------------------------------
                  U001      Week1     Asp-05mg  Headac    RBC               0
                  U001      Week2     Asp-05mg  Vomiting  ELISA             0
                  U002      Week1     Asp-10mg  Rashs     WBC               0
                  U002      Week2     Asp-10mg  Skinprb   WBC               0
                  U003      Week1     Asp-15mg  Fever     PLASMA            0
                  U003      Week2     Placebo   Rashs     RBC               0
                  U001      Week3     Asp-05mg  Headac                      1
                  U002      Week3     Placebo   cold                        1
                  U003      Week3     Asp-15mg  Fever                       1
                  U004      Week1     Asp-05mg                              2
                  U004      Week2               Dizzy                       2
                  U004      Week3                         ABC               2

Re: matching data and non matching data by sql

thanikondharish — Sat, 28 Jul 2018 03:21:42 GMT

Data Mh;
Input Subjid $ Visit $ Dose $;
Cards;
U001 Week1 Asp-05mg
U002 Week1 Asp-10mg
U003 Week1 Asp-15mg
U001 Week2 Asp-05mg
U002 Week2 Asp-10mg
U003 Week2 Placebo
U001 Week3 Asp-05mg
U002 Week3 Placebo
U003 Week3 Asp-15mg
U004 Week1 Asp-05mg
;

Data Ae;
Input Subjid $ Visit $ Aeterm $;
Cards;
U001 Week1 Headac
U002 Week1 Rashs
U003 Week1 Fever
U001 Week2 Vomiting
U002 Week2 Skinprb
U003 Week2 Rashs
U001 Week3 Headac
U002 Week3 cold
U003 Week3 Fever
U004 Week2 Dizzy
;

data lb ;
input subjid $ visit $ test $ ;
cards ;
U001 Week1 RBC
U002 Week1 WBC
U003 Week1 PLASMA
U001 Week2 ELISA
U002 Week2 WBC
U003 Week2 RBC
U004 Week3 ABC
;

data unmatching ;

merge mh(in=a) ae(in=b) lb(in=c) ;

by subjid visit ;

if a=0 or b=0 or c=0 ;

run;

what data shall i get if i submit above data at the same time i want
that data if we will use sql

Re: matching data and non matching data by sql

PGStats — Sat, 28 Jul 2018 04:29:53 GMT

Those would be the rows with missMatch > 0 in the matching dataset above. The missMatch number is the number of datasets where the subjid-visit is missing.

Re: matching data and non matching data by sql

ChrisNZ — Sat, 28 Jul 2018 05:23:17 GMT

1. Can you please use punctuation?

2. What data shall i get if if a=0 or b=0 or c=0 ;
This tells SAS to only keep teh data when one or more table(s) does not have the merge keys.
You could use: if A+B+C ne 3;