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.
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.
Thanks HB. However I have to do this in VA. Incidentally I did figure it out. The solution is in another post here:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.