How to compare the contents of two List Tables

[MikeBsocal]

Hi All

I'm new to VA. I've been going over a related question with Sam_SAS  here on the forum. Maybe I'm trying to solve the problem in the wrong way. I will pose it differently here -- at a higher level.

I have a single data source -- about 2M rows of drug data, split out by a Delivery Date (when we delivered the analysis) and by the Market (the disease state the drugs are used for). I'm creating two List Tables based on this single source of data. I want to compare the contents of these two List Tables. I want to know which NDC11's (this is the unique identifier for a drug) are the same in two List Tables, and which NDC11's are different.

Below we can see a 2022-04 delivery of drugs for Congestive Heart Failure (CHF) on the left. And a 2022-02 delivery of drugs for regular Heart Failure in the middle. The lists are similar with a few exceptions. I'd like the table on the far right to show me the drugs in common to both Markets/Delivery Dates (red is one example). I also want a fourth table (not pictured) that shows the drugs that are different between the two List Tables (green is an example).

I was trying to use flags but I haven't been able to make it work. Anybody know any good solutions? Thanks in advance!  *I'll note that I have to make this interactive, so users can change Markets and Delivery Dates to see the resulting comparisons.

[HB]

I'm sorry- I don't know VA and I don't know how to manipulate the data in VA.

In regular SAS if you had the following

data list1;
   input id;
datalines;
1101
1102
1103
1104
1106
1107
1108
1110
;
run;


data list2;
   input id;
datalines;
1101
1102
1103
1105
1106
1107
1108
1109
;
run;

You could use Proc SQL to create the list types that I think you want:

*show me things in both list1 and list2;
proc sql;
	title "Things in Both Lists";
	select list1.id
	from list1 inner join list2 on list1.id = list2.id;
quit;


*show me things in list1 not in list2;
proc sql;
	title "Things Only in List1";
	select list1.id
	from list1 left join list2 on list1.id = list2.id
	where list2.id is null;
quit;


*show me things in list2 not in list1;
proc sql;
	title "Things Only in List2";
	select list2.id
	from list2 left join list1 on list2.id = list1.id
	where list1.id is null;
quit;


*show me things in only one list and which one it is in;
proc sql;
	title "Things Only in One of the Lists";
	select "Only in List1", list1.id
	from list1 left join list2 on list1.id = list2.id
	where list2.id is null
	union
	select "Only in List2", list2.id
	from list2 left join list1 on list2.id = list1.id
	where list1.id is null;
quit;

Results:

Things in Both Lists	
id	
1101	
1102	
1103	
1106	
1107	
1108	
	
	
Things Only in List1	
id	
1104	
1110	
	
	
Things Only in List2	
id	
1105	
1109	
	
	
Things Only in One of the Lists	
	id
Only in List1	1104
Only in List1	1110
Only in List2	1105
Only in List2	1109

I don't know if you can use Proc SQL in VA to make a table that you then draw against for a visual, but I thought i would post something since you had no replies.  

[MikeBsocal]

Thanks HB. However I have to do this in VA. Incidentally I did figure it out. The solution is in another post here:

https://communities.sas.com/t5/SAS-Visual-Analytics/SAS-VA-how-to-create-calculations-using-columns-...