SQL Compute each buyer's spending total, presuming the transaction amount would have to be computed as transaction_amount = price * unit proc sql; create table buyer_totals as select buyer, sum(price*unit) as spend_total from myData group by buyer ; ... View more

Ran your code and didn't get any errors.  What version of SAS are you running (%put &SYSVLONG4) ?  What client (PC sas, EG, Studio, other) are you using ? Can you post or attach the entire log after running the code in a fresh session ? ... View more

If all these *_TXT and *_DASH macro symbols are to be used to process many variables in a single data set you would be better off using arrays.  Personally, when my coding takes me down the path of "I am using a lot of symbols as if they were data" I take a step back and ask myself an old lumberjack question "Why am I trying to send these logs down the river sideways?"   ... View more

The VNEXT call routine will iterate through the PDV variable names.  The first call will return the first variable name. Use the returned variable name in a DOSUBL function call that runs a 'slicing' step.   Example: data _null_; if 0 then set sashelp.cars; length _name_ $32; call vnext(_name_); rc = dosubl("data want; set sashelp.cars (keep=" || trim(_name_) || ");"); stop; run; ... View more

Maybe you should try to explain what you are trying to do.  There is probably a much cleaner approach.   Regardless, since macro variable names do not contain special characters, you can pass a single argument that lists all the variables separated by spaces. Rather than rewriting your existing macro, you can create a new one that parses out each name and invokes the original macro that operates on a single variable. There are many ways to parse out a 'word' from a space separated list of words, SCAN is one of the easiest and most accessible.   Example: %macro newDatesX (names); %local index name; %do index = 1 %to %sysfunc(countw(&names)); %let name = %scan(&names,&index); %newDates(&name) %mend; %mend; %newDatesX( A_10 A_11 B_10 B_11 C_10 C_11 ... hundreds more ? ... ) ... View more

A HASH object is very effective for counting unique values (.NUM_ITEMS) when the variable is a key, and for entering into membership via .ADD()   A logic statement with a compiled IN operation is said to be one of the fastest methods for checking set membership. Use IN to see if a doctors diagnosis is one that is of interest (for say a study) Use HASH to track unique diagnosis in the study list Use another HASH to track non-study diagnoses (for yucks) Example: Data for 1,000 patients having random number of visits and random number of random diagnoses. data have; call streaminit(123); do patid = 1 to 100; date = today() - rand('integer',250,1300); top = rand('integer',5,1000); do index = 1 by 1 while (date <= today() and index <= top); array diag(24); do dindex = 1 to rand('integer',1,rand('integer', dim(diag))); * possible repeated diagnosis in row dont matter in this example; * so dont try to prevent them; diag(dindex) = rand('integer',1,1000); end; visitid + 1; output; date + rand('integer',0,3); call missing (of diag(*)); end; end; keep patid date diag: visitid; format date yymmdd10.; run; * visit count distribution, just a look see; proc sql; create table freq1 as select visit_count, count(*) as freq from ( select patid, count(*) as visit_count from have group by patid ) group by visit_count ; Per patient, count number of diagnoses matching the study list across all diagnoses of all visits. %let study_diagnoses = 2,5,11,17,23,31,43,53,61,71,79,89; data want; if _n_ = 1 then do; call missing(dx); declare hash study_dx(); declare hash other_dx(); study_dx.defineKey('dx'); study_dx.defineDone(); other_dx.defineKey('dx'); other_dx.defineDone(); end; do until (last.patid); set have; by patid; array dxs diag:; do index = 1 to dim(dxs) while (not missing(dxs(index))); dx = dxs(index); if dx in (&study_diagnoses) then rc = study_dx.add(); else rc = other_dx.add(); end; end; dx_in_study_count = study_dx.num_items; dx_not_in_study_count = other_dx.num_items; study_dx.clear(); other_dx.clear(); keep patid dx_:; run;   ... View more

What is your group by ?  If you don't group by var1, var2, var3 SQL will automatically remerge the sums.   Have you tried using Proc MEANS to compute the sums?  MEANS is very likely much easier to code than what you are attempting and does not require macro.   Example: Simulated data * simulate some data; data have; call streaminit(123); do one = 1 to 3; do two = 1 to 10; do three = 1 to 5; do instance = 1 to 20 + rand('integer', 2,12); array x a_count e_count_gp e_count_be19 e_count_prc; do over x; x = rand('integer', 1, 20); end; output; end; end; end; end; drop instance; run; Proc MEANS   proc means noprint data=have; by one two three; output out=sums sum= / autoname; run;     ... View more

As stated by others, you are instantiating and loading a hash for every row that meets a select criteria.  The new instantiations overwrite the hash reference and thus any memory associated with the prior instance exists but is unreachable.  Congratulations, you have created a memory leak!   Your code indicates you want to only load hash data when it is needed according to some select condition (lets call this dynamic hash instance loading), versus pre-loading all possibly needed hash data (static hash instance loading).   Going down the dynamic path...   You can declare a hash in a non-executable statement   declare hash h; But there is no way to test if h 'null', which would mean there is no hash instance associated with it.   Any attempt to use a hash method will log errors   ERROR: Uninitialized object at line #### column ##. ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase. You can declare an empty hash instance, but instantiation is an executable operation and needs to be guarded against repeated execution using an _n_=1 block.     NUM_ITEMS=0 logic will tell you the hash has not been loaded (dynamically) yet.  If your hash data loading data set has 0 rows, that is another problem for you to deal with.   if _n_ = 1 then do; * declare an 'empty' instance once but don't load. num_items can be used and will return 0. * This takes up a tiny amount of memory; declare hash h(); end; The dynamic loading of a hash will create a new instance and populate it.     when (<condition>) do; if h.num_items=0 then do; * dynamic loading per condition; * load once; h = _new_ hash (dataset:'<tablename>'); h.defineKey('<key-column>'); h.defineData('<data-column>'); h.defineDone(); end; ... ... some code with a h.<hash-method>() ...   Example code with three hash objects dynamically loaded data lookup1; do key = 1 to 10; value = key**2; output; end; run; data lookup2; do key = 1 to 10; value = key**1/2; output; end; run; data lookup3; do key = 1 to 10; value = 1e6+key; output; end; run; data have; input lookup_table key @@; datalines; 1 2 3 4 1 3 2 4 5 1 1 1 2 2 3 3 3 10 ; data want; if _n_ = 1 then do; * declare an 'empty' instance once but don't load. num_items will be 0. * This takes up a tiny amount of memory; declare hash lookup1(); declare hash lookup2(); declare hash lookup3(); end; set have; select (lookup_table); when (1) do; if lookup1.num_items=0 then do; * load (once) hash data on requirement demand (the when condition); lookup1 = _new_ hash (dataset:'lookup1'); lookup1.defineKey('key'); lookup1.defineData('value'); lookup1.defineDone(); end; value = lookup1.find(); end; when (2) do; if lookup2.num_items=0 then do; * load once; lookup2 = _new_ hash (dataset:'lookup2'); lookup2.defineKey('key'); lookup2.defineData('value'); lookup2.defineDone(); end; value = lookup2.find(); end; when (3) do; if lookup3.num_items=0 then do; * load once; lookup3 = _new_ hash (dataset:'lookup3'); lookup3.defineKey('key'); lookup3.defineData('value'); lookup3.defineDone(); end; value = lookup3.find(); end; otherwise value = .; end; run;   ... View more

SQL does not have a looping process to create multiple rows from a single row.  DATA step does.   Create a DATA step view that outputs rows for each mid-month point from START to END.  Then join the view with the DATA on view's dates   Example: data accounts; input acct start_date: date9. end_date: date9.; format start_date end_date date9.; datalines; 1234 15JAN2020 15MAY2020 6789 27MAR2020 27MAY2020 ; data dates_v / view=dates_v; set accounts; do index = 0 by 1 until (date >= end_date or index >= 1000); date = intnx('month', start_date, index, 'SAME'); output; end; format date date9.; keep acct date; run; Then  create table want as select data.* from dates_v as V left join data on data.acct = V.acct and data.id_date = V.date   ... View more