I didn't know DS2 either, SAS is full of surprises 🙂 @Ksharp could you show me how would you solve the problem using DS2? Thanks! ... View more

no no sorry I meant that 7 minutes are needed to create the table, plus more 5 minutes to recreate the index, so total 12 minutes, versus 20 minutes of MODIFY solution (which does all in one statement) ... View more

Yes, approximately 5 minutes ... View more

Hi guys, I tried all your suggestions, here are the results. (HUGE is 70GB, SMALL is 10MB)   HASH solution, by @KachiM: amazing, it does all the job in 7 minutes, including CPU time and I/O time (writing 70GB on disk takes time, just doing "cp HUGE HUGE2" takes 3 minutes) Pro: fastest solution Cons: since it creates a new table, it wastes I/O time, needs double disk space, and needs to recreate the index after the operation Comments: I didn't know hashes, so I read about them on documentation; in your opinion, do you think that loading both tables in memory would speed up more? Are there more sophisticated strategies to increase performance with hashes? Moreover, what happens if the system does not have enough ram to hold the tables, it just slows down or sas crashes? DELETE solution, by @PGStats: thank you for the correct syntax, it will be surely useful! However after 1 hour I aborted it. Pro: Rollback available, no double space, no need to recreate the index Cons: too much time Comments: too much time for production environment MODIFY solution, by @ChrisNZ: quite good solution, it takes about 20 minutes to complete Pro: no double space, no need to recreate the index, quite fast Cons: not as fast as HASH MODIFY with HASH solution, by Me: data HUGE; modify HUGE; if _n_ = 1 then do; declare hash h(dataset:'SMALL'); h.definekey('K1','K2'); h.definedone(); end; if h.find() ^= 0 then remove; run; I taught I had a good idea mixing your solution, however after 1 hour I aborted it    So I think I'll use the first solution, any other comments and suggestions are wery welcome!   Thanks a lot Regards   ... View more

Thank you for you answer, however users should be able to use the export feature to export simple data to excel... So there is no way to keep export enabled while preventing the user to download detailed data?   The purpose should be that the user should be able to export only what he sees.   Example scenario: we have a table with REGION | CUSTOMER_ID; we want to show the distribution of customers on a regional map; hence, we create a report with a regional map with REGION and COUNT(CUSTOMER_ID): then, if a user wants to export this data to excel (i.e. to do something else) he should export only this grouped table in excel, and NOT the CUSTOMER_IDs themselves! This does not make sense for us since if we liked to let the user to export the entire data we would provide him with direct access to the table instead of building a report...  A hypothetical solution would be to directly load in VA the grouped table REGION|COUNT, so 1) the regional map would keep working and 2) even if the user would download the detailed data, all he would get is the grouped table REGION|COUNT. However, this "solution" requires to add a middle step of preprocessing to trasform our table REGION | CUSTOMER_ID in REGION | COUNT(CUSTOMER_ID), and this is extremely unconfortable and for all big tables and reports we use.   What would you recommend? Thanks a lot Regards ... View more

Thank you for your answer, but unfortunately I'm not allowed to do that by the admin, all I can do is "Create Report" section and use this table as is... ... View more