I have a large table of information that goes something like this: Person Code Abby A123 Bob A123 Chris B456 Abby B456 Deb C789 Bob B456 ... ...   I want to map each person to their codes.  For instance: Person Codes Abby A123, B456 Bob A123, B456 Chris B456 Deb C789 ... ...   In essence, I want to associate each person with a list of their codes.  I'm just not sure how to do so - what data structure to use and what procedures to do it with. ... View more

I have a large table (HAVE) with variables Units, Code, and Year.  I'm trying to create a summary of counts by code and year:   proc sql;     create table WANT as     select Code, Year, sum(Units) as Total     from HAVE     group by Code, Year quit;   This works, except there are some combinations of Code and Year that have no Units, which means the resulting table doesn't include those combinations at all.  I want to include all combinations, and show 0 in the Total column when there are no Units.  I've tried playing with the coalesce() function [as coalesce(sum(Units),0) and as sum(coalesce(Units,0))] but it hasn't given me the desired result so far.   Current result: Code    Year    Total A           2020   1 B           2020   3 B           2021   7   Desired result: Code    Year    Total A           2020   1 B           2020   3 A           2021   0 B           2021   7 ... View more

I want to create a macro variable that contains multiple values to check against.  Example pseudocode:   %LET myCodes = [Not sure how to do this part];     /*Ideally &myCodes would return a list/array of the codes I wanted.  i.e. Code1, Code2,...*/   ...   proc sql;     create table myTable as     select * from DataTable    /*Note:  Assume DataTable has a column named Code*/     where Code in &myCodes; quit;   /pseudocode   What I am having trouble with is defining myCodes.  I had the thought of just defining it as     %LET myCodes = Code1 Code2 ...; but that would just make myCode be equivalent to a single long string, correct?  I'd imagine that has the potential to cause problems.  Even if I separated them (e.g. 'Code1','Code2',...) my understanding is that I would just end up with a string that contains all the punctuation.   Am I misunderstanding something or is there a different technique to do this?   Edit: The Codes are alphanumeric (e.g. A1234). The values in myCodes will be entered by hand. I'm trying to filter a dataset for observations with alphanumeric CODE that matches one of the codes in myCodes. ... View more