Apparently the problem was that I didn't use separate input and output data sets.  Below is the final version of the code.  Thanks! /*******************************************************************************/ DATA medicaid.&filename._MHvisit; SET medicaid.&filename._DXcount; /*Initialize variable to represent if the visit included a mental health diagnosis*/ MHvisit = 0; array dx_cat{248} dx_cat_1-dx_cat_248; DO i = 1 to 248;     IF DX_cat(i) = 5 THEN MHvisit = 1; END; IF MHvisit=1 THEN output; RUN; ... View more

No luck   I tried it both ways.  The DO loop outputs a file with 250 variables and 0 observations.  The whichn version outputs a file with 249 variables and 0 observations. Here is the code I used (the DX_cat variables actually go up to 248). DATA medicaid.&filename._DXcount; /*Initialize variable to represent if the visit included a mental health diagnosis*/ MHvisit = 0; array dx_cat(248) dx_cat_1-dx_cat_248; DO i = 1 to 248;     IF DX_cat(i) = 5 THEN MHvisit = 1; END; IF MHvisit=1 THEN output; RUN; DATA medicaid.&filename._DXcount; /*Initialize variable to represent if the visit included a mental health diagnosis*/ MHvisit = 0; array dx_cat(248) dx_cat_1-dx_cat_248; mhvisit=1; if whichn(5, of dx_cat(*))>0 then output; RUN; ... View more

Sorry I haven't been more prompt in replying -- we had another project come up that took precedence.  And now w/ the holiday break coming up, I probably won't get back to this till January. snoopy369 wrote: Is your syntax the same for each state/year/etc.?  So you've got something like proc means data=TX_DATA_2005; var OUTCOME1 OUTCOME2; outout out=TX_OUTPUT_2005 mean=; run; and you want to alter "TX" and "2005" ? You may be able to do this without a macro, depending on your analysis.  Many analyses can be done with BY or CLASS to handle the the states. For example, if you have one dataset MYDATA, and it has STATE and YEAR as variable: Yeah that's the basic idea (though the syntax is a lot more complicated).  The problem with starting from the original source file that has all 50 states is that the files are massive (about 320GB).  It takes about 3hrs just to extract the data for 1 state for 1 year.  That's why I have separate files for each state/year combination.  I could take just the 6 states of interest and combine them into a smaller source file, but I still think that would result in much slower processing times.  Besides, the states of interest will change depending on the disease we're investigating. Also, I'm actually unsure what you're question is.  You've stated the need for a macro, so are you: 1. Looking to hire a programmer to convert code to macro 2. Looking for references 3. Looking for instructions 4. Looking for a helpful person to read your mind and magically understand your data structure, code and create a macro and post it here 5. Looking for how to convert your current code (you would need to post it) to a macro. Though I highly suggest reading above paper and trying yourself first. Well #4 would be great, actually :smileysilly:  But a combination of 2, 3, and maybe a bit of 4 is more realistic!  I took a seminar on SAS macros, but it was several years ago and I never actually wrote any macros.  So I have very limited knowledge on the subject.  The paper u posted looks like it will be helpful, so maybe the best thing to do at this point is try to write as much of the macro as I can based on the paper and what snoopy369 posted, and then bring that back here for advice on how to tweak it. Regardless of who does the coding, make sure that the macro works for Oregon and Nebraska.  Their abbreviations (OR and NE) are also significant words in macro language and, depending on how they are used in the macro, can require special handling. Good to know.  I'm not sure we'll be using those states at any point, but I'll keep it in mind! ... View more

There are a lot of restrictions and criteria that have to be met for in this syntax, and accordingly, the file merges, record counts, deduplicating, etc have to be done in a certain order to accurately identify the correct IDs.  So I can't do the merges inside the hash table.  I've created a flowchart for the syntax to help me visualize all the steps, and I've been careful about making it as efficient as possible while still finding the correct IDs. Anyway, I'm using the syntax below and it works great -- it finds the correct IDs (which I verified by spot-checking) and is relatively fast.  There is just 1 more issue.  Given the size of the files I'm dealing w/, I'd prefer to write the temp files to a specific directory, rather than the default work directory.  The C:\ drive on this computer is a 256GB SSD, while the D:\ drive is a 6TB RAID 10 array.  Is there any way to assign the dataset "inter" to the "mi." data library? /* de-dup source keys by loading into hash */ data mi.ipot2007_mi_meas13_dedup;   set     mi.ipot2007_mi_meas13_cut (keep=MSIS_ID SRVC_BGN_DT)     end=last;   ;   if _n_=1 then     do;       declare hash h0(ordered:'y',multidata:'n',hashexp:8);       _rc=h0.defineKey('MSIS_ID','SRVC_BGN_DT');       _rc=h0.defineData('MSIS_ID','SRVC_BGN_DT');       _rc=h0.defineDone();     end;   _rc=h0.ref();   /* write hash to target data set "want" */   if last then     h0.output(dataset:'inter');         /*IS IT POSSIBLE TO ASSIGN A DATA LIBRARY TO 'INTER'?*/ run; proc sql;     create table mi.ipot2007_mi_Meas13Had3 as     select MSIS_ID,         count(distinct SRVC_BGN_DT) as numsepvisits     from work.inter     group by MSIS_ID     having count(distinct SRVC_BGN_DT) >= 3; quit; ... View more

Patrick wrote: So how should your final output data set look like? So far I've understood you want one row per id and date. Yeah that wasn't very clear.  Part of the problem is that the higher-ups at my work redefined (and expanded) the task after I had already started working on it.  I had working syntax that could have done the counting, but I'm not sure how to adapt it to the expanded task.  I've also never worked with hash tables before, so I'd have to adapt it to work w/ that as well. The final data file should have a single record for each ID.  Ideally, I like it to have the ID variable and the count variable, so I can spot-check the output.  The cumulative frequency for the ID variable in the output would provide the total number of people identified.  Based on my example above, the output data file would be 1111      3 2222     3 3333     4 And the frequency table would like something like this (ID, freq, cumulative freq) 1111     1     1 2222     1     2 3333     1     3 Also, I don't really understand why there are 2 "have" tables.  I'm not sure which of my file names I should put there.  ... View more

I tried the hash table approach as well, and that seems to work.  However, it doesn't count the ID's properly.  So for example, if the file "want" looks like this: 1111    03012008     1111    04012008     1111    09012008     2222    03012008     2222    04012008 2222    06172008 3333    03012008     3333    04012008     3333    09012008 3333    09082008 Then the final count should be 3, becuz there are 3 different IDs that have at least 3 healthcare encounters on separate dates.  Instead, the count will be 10, becuz there are 10 different records. ... View more

Ok, I figured out a little bit more about what's going on.  SRVC_BGNCount is counting the total number of unique service begin dates (SRVC_BGN_DT) in the file.  In other words, it's eliminating duplicates across the entire file, not just w/ respect to the ID variable.  The value is 365, which is greater than 3, so every record is included in the output file. Sorry about the confusion from the syntax I pasted... the forum software kept reformatting it.  I tried pasting it several times and even tried to manually move it around, but to no avail. ... View more

Sorry I haven't gotten back to this... unfortunately the project has become larger in scope, there are other files that have to be merged in, variable names have be changed so they match across files, etc.  So I had to rewrite what u wrote above:    Proc SQL;  Create Table mi.ipot2007_mi_Meas13_dedup  as Select Distinct a.MSIS_ID,          a.SRVC_BGN_DT  From mi.ipot2007_mi_Meas13_CUT a,  (Select MSIS_ID,  Count (Distinct SRVC_BGN_DT) as SRVC_BGNCount  From mi.ipot2007_mi_Meas13_CUT) b  Where a.MSIS_ID = b.MSIS_ID and SRVC_BGNCount >= 3    Order by a.MSIS_ID, a.SRVC_BGN_DT;  Quit; title1 'ipot2007_mi_Meas13_dedup'; proc freq; tables MSIS_ID SRVC_BGN_DT /*SRVC_BGNCount*/; run; The problem is that SAS seems to ignore the subquery -- SRVC_BGNCount doesn't even exist in the resulting data file. Likewise, it also ignores the "and SRVC_BGNCount >= 3" part of the Where statement.  It doesn't even give me an error, which is should becuz that part of the Where statement refers to a variable that doesn't exist in the file.  I tried removing it and that didn't change the results at all. Isn't there any way to get this forum to display plain text w/o reformatting it?? ... View more