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;
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:
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.