If I have a loop code, that says the following: data _null_; set pc_temp; if (mod(_n_,100)=0 then count_loops = (int(_n_/100)); else count_loops = sum(1, int(_n_/100)); call symput ('loops needed',  count_loops); run; My concern with this is it looks at 100 records at a time until it is complete. Most of the projects we work on are based on pulling data from our claims system that is for an entire year. We process approx. 286-300 million claims per year. I am reluctant to do a loop that looks at 100 records at a time because that years worth of data might result in over 8 millions records and reading 100 at a time I would think would take forever. If I change the loop to look at 1 million records at a time and loop, will that cause my PC to crash, or my server? When I query our claims system, I do it remotely. I telecommuter and connect through VPN. So, another concern is the system might timeout. Not VPN but on the server side. I guess another concern is if I am looping at 1 million at a time, since it is through VPN, would it be slower than molasses? ... View more

My proc transpose is not turning out the way I need it to. I did a groupby in a proc sql to get the data in a table that I needed but I am not getting my proc transpose to look right. Here is an example of my table: strata descript caths pcis mpis imppacs secho pet tte tee revascs perfs cts cabgs stemi nstemi mbr 582563008 715 582563008 IP 26 6 4 218 11 7 55 2 201 582563008 OP 102 7 10 4 156 7 17 140 1 234 582563008 HOSPICE 1 1 582563008 OFFICE 5 1129 509 1128 582563008 OTHER IP 1 582563008 OTHER OP 2 3 2 When I do the proc transpose I get this: strata descript _NAME_ metric1 582563008 caths 582563008 pcis 582563008 mpis 582563008 imppacs 582563008 secho 582563008 pet 582563008 tte 582563008 tee 582563008 revascs 582563008 perfs 582563008 cts 582563008 cabgs 582563008 stemi 582563008 nstemi 582563008 mbr 715 582563008 outpt_pci 582563008 maj_restudy 582563008 min_restudy 582563008 restudy_denom 582563008 PCI_FU_30 582563008 FU30_denom 582563008 pci_fu_182 582563008 FU182_denom 582563008 ACUTE-CARE INPATIENT FACILITY caths 26 582563008 ACUTE-CARE INPATIENT FACILITY pcis 6 582563008 ACUTE-CARE INPATIENT FACILITY mpis 582563008 ACUTE-CARE INPATIENT FACILITY imppacs 4 582563008 ACUTE-CARE INPATIENT FACILITY secho 582563008 ACUTE-CARE INPATIENT FACILITY pet 582563008 ACUTE-CARE INPATIENT FACILITY tte 218 582563008 ACUTE-CARE INPATIENT FACILITY tee 11 582563008 ACUTE-CARE INPATIENT FACILITY revascs 7 582563008 ACUTE-CARE INPATIENT FACILITY perfs 55 582563008 ACUTE-CARE INPATIENT FACILITY cts 582563008 ACUTE-CARE INPATIENT FACILITY cabgs 582563008 ACUTE-CARE INPATIENT FACILITY stemi 582563008 ACUTE-CARE INPATIENT FACILITY nstemi 2 582563008 ACUTE-CARE INPATIENT FACILITY mbr 201 582563008 ACUTE-CARE INPATIENT FACILITY outpt_pci 582563008 ACUTE-CARE INPATIENT FACILITY maj_restudy 582563008 ACUTE-CARE INPATIENT FACILITY min_restudy 582563008 ACUTE-CARE INPATIENT FACILITY restudy_denom 5 582563008 ACUTE-CARE INPATIENT FACILITY PCI_FU_30 1 582563008 ACUTE-CARE INPATIENT FACILITY FU30_denom 6 582563008 ACUTE-CARE INPATIENT FACILITY pci_fu_182 1 582563008 ACUTE-CARE INPATIENT FACILITY FU182_denom 5 582563008 ACUTE-CARE OUTPATIENT FACILITY caths 102 582563008 ACUTE-CARE OUTPATIENT FACILITY pcis 7 582563008 ACUTE-CARE OUTPATIENT FACILITY mpis 582563008 ACUTE-CARE OUTPATIENT FACILITY imppacs 10 582563008 ACUTE-CARE OUTPATIENT FACILITY secho 4 582563008 ACUTE-CARE OUTPATIENT FACILITY pet 582563008 ACUTE-CARE OUTPATIENT FACILITY tte 156 582563008 ACUTE-CARE OUTPATIENT FACILITY tee 7 582563008 ACUTE-CARE OUTPATIENT FACILITY revascs 17 582563008 ACUTE-CARE OUTPATIENT FACILITY perfs 140 582563008 ACUTE-CARE OUTPATIENT FACILITY cts 582563008 ACUTE-CARE OUTPATIENT FACILITY cabgs 582563008 ACUTE-CARE OUTPATIENT FACILITY stemi 582563008 ACUTE-CARE OUTPATIENT FACILITY nstemi 1 582563008 ACUTE-CARE OUTPATIENT FACILITY mbr 234 582563008 ACUTE-CARE OUTPATIENT FACILITY outpt_pci 4 582563008 ACUTE-CARE OUTPATIENT FACILITY maj_restudy 582563008 ACUTE-CARE OUTPATIENT FACILITY min_restudy 2 582563008 ACUTE-CARE OUTPATIENT FACILITY restudy_denom 6 582563008 ACUTE-CARE OUTPATIENT FACILITY PCI_FU_30 582563008 ACUTE-CARE OUTPATIENT FACILITY FU30_denom 7 582563008 ACUTE-CARE OUTPATIENT FACILITY pci_fu_182 582563008 ACUTE-CARE OUTPATIENT FACILITY FU182_denom 4 582563008 INPATIENT HOSPICE caths 582563008 INPATIENT HOSPICE pcis 582563008 INPATIENT HOSPICE mpis 582563008 INPATIENT HOSPICE imppacs 582563008 INPATIENT HOSPICE secho 582563008 INPATIENT HOSPICE pet 582563008 INPATIENT HOSPICE tte 1 582563008 INPATIENT HOSPICE tee 582563008 INPATIENT HOSPICE revascs 582563008 INPATIENT HOSPICE perfs 582563008 INPATIENT HOSPICE cts 582563008 INPATIENT HOSPICE cabgs 582563008 INPATIENT HOSPICE stemi 582563008 INPATIENT HOSPICE nstemi 582563008 INPATIENT HOSPICE mbr 1 582563008 INPATIENT HOSPICE outpt_pci 582563008 INPATIENT HOSPICE maj_restudy 582563008 INPATIENT HOSPICE min_restudy 582563008 INPATIENT HOSPICE restudy_denom 582563008 INPATIENT HOSPICE PCI_FU_30 582563008 INPATIENT HOSPICE FU30_denom 582563008 INPATIENT HOSPICE pci_fu_182 582563008 INPATIENT HOSPICE FU182_denom 582563008 OFFICE/CLINIC caths 582563008 OFFICE/CLINIC pcis 582563008 OFFICE/CLINIC mpis 582563008 OFFICE/CLINIC imppacs 582563008 OFFICE/CLINIC secho 5 582563008 OFFICE/CLINIC pet 582563008 OFFICE/CLINIC tte 1129 582563008 OFFICE/CLINIC tee 582563008 OFFICE/CLINIC revascs 582563008 OFFICE/CLINIC perfs 509 582563008 OFFICE/CLINIC cts 582563008 OFFICE/CLINIC cabgs 582563008 OFFICE/CLINIC stemi 582563008 OFFICE/CLINIC nstemi 582563008 OFFICE/CLINIC mbr 1128 582563008 OFFICE/CLINIC outpt_pci 582563008 OFFICE/CLINIC maj_restudy 582563008 OFFICE/CLINIC min_restudy 582563008 OFFICE/CLINIC restudy_denom 582563008 OFFICE/CLINIC PCI_FU_30 582563008 OFFICE/CLINIC FU30_denom 582563008 OFFICE/CLINIC pci_fu_182 582563008 OFFICE/CLINIC FU182_denom 582563008 OTHER INPATIENT FACILITY caths 582563008 OTHER INPATIENT FACILITY pcis 582563008 OTHER INPATIENT FACILITY mpis 582563008 OTHER INPATIENT FACILITY imppacs 582563008 OTHER INPATIENT FACILITY secho 582563008 OTHER INPATIENT FACILITY pet 582563008 OTHER INPATIENT FACILITY tte 582563008 OTHER INPATIENT FACILITY tee 582563008 OTHER INPATIENT FACILITY revascs 582563008 OTHER INPATIENT FACILITY perfs 1 582563008 OTHER INPATIENT FACILITY cts 582563008 OTHER INPATIENT FACILITY cabgs 582563008 OTHER INPATIENT FACILITY stemi 582563008 OTHER INPATIENT FACILITY nstemi 582563008 OTHER INPATIENT FACILITY mbr 582563008 OTHER INPATIENT FACILITY outpt_pci 582563008 OTHER INPATIENT FACILITY maj_restudy 582563008 OTHER INPATIENT FACILITY min_restudy 582563008 OTHER INPATIENT FACILITY restudy_denom 582563008 OTHER INPATIENT FACILITY PCI_FU_30 582563008 OTHER INPATIENT FACILITY FU30_denom 582563008 OTHER INPATIENT FACILITY pci_fu_182 582563008 OTHER INPATIENT FACILITY FU182_denom 582563008 OTHER OUTPATIENT PLACE OF SERVICE caths 582563008 OTHER OUTPATIENT PLACE OF SERVICE pcis 582563008 OTHER OUTPATIENT PLACE OF SERVICE mpis 582563008 OTHER OUTPATIENT PLACE OF SERVICE imppacs 582563008 OTHER OUTPATIENT PLACE OF SERVICE secho 582563008 OTHER OUTPATIENT PLACE OF SERVICE pet 582563008 OTHER OUTPATIENT PLACE OF SERVICE tte 2 582563008 OTHER OUTPATIENT PLACE OF SERVICE tee 582563008 OTHER OUTPATIENT PLACE OF SERVICE revascs 582563008 OTHER OUTPATIENT PLACE OF SERVICE perfs 3 582563008 OTHER OUTPATIENT PLACE OF SERVICE cts 582563008 OTHER OUTPATIENT PLACE OF SERVICE cabgs 582563008 OTHER OUTPATIENT PLACE OF SERVICE stemi 582563008 OTHER OUTPATIENT PLACE OF SERVICE nstemi 582563008 OTHER OUTPATIENT PLACE OF SERVICE mbr 2 582563008 OTHER OUTPATIENT PLACE OF SERVICE outpt_pci 582563008 OTHER OUTPATIENT PLACE OF SERVICE maj_restudy 582563008 OTHER OUTPATIENT PLACE OF SERVICE min_restudy 582563008 OTHER OUTPATIENT PLACE OF SERVICE restudy_denom 582563008 OTHER OUTPATIENT PLACE OF SERVICE PCI_FU_30 582563008 OTHER OUTPATIENT PLACE OF SERVICE FU30_denom 582563008 OTHER OUTPATIENT PLACE OF SERVICE pci_fu_182 582563008 OTHER OUTPATIENT PLACE OF SERVICE FU182_denom I am not sure how to do the output I need because I need the mbr to be summed overall regardless of descript and then for example outpatient_perfs 2/2281 (2281 is the sum of the members) My ultimate goal is to have a final rtf that looks like this: Metric Component                                Calculation IP_Caths                                                   26/2281 IP_PCIs                                                      6/2281 IP_Imppacs                                                4/2281 etc on down all the procedures and then I have to breakout the out patients and by procedures, etc. I just did not want to have to do a ton of proc sqls or other types of tables to end up with the final result. If I have to I will but thought there might be something better out there. A better process. ... View more

I have a table that I am trying to find a way to do this smart. The table has over 8 million rows and when I groupby my taxID's there are a little over 80k. I want to be able to groupby my TAXID, the procedure and the place of service. I know I can do a case statement, but that becomes a long process because I would have a ton of case statements and think there has to be a better way. Maybe not. Here is an example of the table. taxid srvc_dt perf echo pet tte tee cabg stemi pl of srvc id pl of srvc desc 1 1/1/2010 1 1 office 1 12/1/2011 1 4 lab 1 11/4/2009 1 8 inpatient 1 5/25/2011 1 12 outpatient 1 3/2/2012 1 15 hospice 1 1 1 1 1 1 1 1/15/212 1 20 rehab 1 1/31/2012 1 25 other 2 2/5/2012 1 1 office 2 6/5/2012 1 4 lab 2 6/7/2012 1 8 inpatient 2 1 2 1 2 7/1/2012 1 15 hospice 2 7/15/2012 1 20 rehab 2 1/1/2013 1 25 other I tried doing a case statement but with having so many rows it seems to be kind of messy to do it like that. Just wondering if there is a better way. My code possible code would look something like this: proc sql; create table temp as (select *, case when pl_of_srvc_id in (1) and perf is not null and srvc_dt is not null then 1 end as perf_1, case when pl_of_srvc_id in (4) and perf is not null and srvc_dt is not null then 1 end as perf_4, case when pl_of_srvc_id in (8) and perf is not null and srvc_dt is not null then 1 end as perf_8, case when pl_of_srvc_id in (12) and perf is not null and srvc_dt is not null then 1 end as perf_12, case when pl_of_srvc_id in (15) and perf is not null and srvc_dt is not null then 1 end as perf_15, case when pl_of_srvc_id in (20) and perf is not null and srvc_dt is not null then 1 end as perf_20, case when pl_of_srvc_id in (25) and perf is not null and srvc_dt is not null then 1 end as perf_25 Note, I am only using perf but hope you get the picture. I have to code each of the procedures and each of the places of services because a perf will be in more than just the office which is 1. In the 8 million row table, all procedures show up in the above places of services so a case statement is required for each so all are counted. So, I end up with 7 case statements for every procedure with a total of 56 case statements. Then I would have to take and group by the TIN and then do another proc sql to sum it all up in another. Just hoping there is faster way of doing this. ... View more