It must be working now, still running after 45min. ... View more

There is a large number of iterations, about 500K total. I reformulated the macro as you describe to perform the proc sql once per iteration and it seems to work right now. Its been running for about 10min which is longer than the normal 3min. So I might have a solution. more Message was edited by: SteveONCSU ... View more

So I've got the macro written now, thanks to a little old fashioned reading. But I keep getting a segmentation error when running it: Segmentation Violation In Task [ SQL (2) ] Fault and traceback information not available Task Traceback The macro runs and inserts values into the table correctly, it just doesn't finish which does not give me the entire dataset I need. Here is the macro, any suggestions would be appreciated: %macro iterate; proc sql noprint; %do u=1 %to &cntunits; %do s=1 %to &cntservices; insert into InitialTickets select orig.unit_pk, orig.service_cat_desc, orig.so_pk, min(orig.sub_prod_pckg_pk) as pk from conx.subscriber_services as orig where orig.so_pk is not null and orig.unit_pk = &&Units&u and orig.service_cat_desc = &&Services&s group by orig.unit_pk, orig.service_cat_desc, orig.so_pk ; %end; %end; quit; %mend iterate; ... View more

Now I'm stuck with this error: ERROR: The %DO statement is not valid in open code. This is the full code of what I have right now: LIBNAME CONX BASE "/sas_perm_data/Enlighten_Landing" ; %global s; /** Passthrough SQL to load distinct service category types ***/ proc sql; connect to odbc as myconn (dsn=Enlighten); create table ServiceCategories as select * from connection to myconn ( select distinct cb_cntr_service_category.service_cat_desc as ServiceCat from cb_subs_prod_pckg as orig left outer join cb_cntr_service_type on orig.service_type_code = cb_cntr_service_type.service_type_code left outer join cb_cntr_service_category on cb_cntr_service_category.service_cat_code = cb_cntr_service_type.service_cat_code ); quit; /** Passthrough SQL to load possible units ***/ proc sql; connect to odbc as myconn (dsn=Enlighten); create table PossibleUnits as select * from connection to myconn ( select distinct unit_pk from cb_so_unit ); quit; /**Load service categories into numbered macro variables*/ proc sql noprint; select servicecat into :Services1 - :Services20 from ServiceCategories; quit; %let cntservices=&sqlobs; /*count of services */ /**Load units into numbered macro variables*/ proc sql noprint; select unit_pk into :Units1 - :Units70000 from PossibleUnits; quit; %let cntunits=&sqlobs; /* Count of units */ /*Create table to hold returned values*/ proc sql noprint; create table test (unit_pk num, service_cat_desc char(30), so_pk num, pk num); quit; /**Loop through all possible units and service categories and insert into table*/ proc sql; %do s=1 %to &cntservices; /* just through services for now for testing*/ insert into test select orig.unit_pk, orig.service_cat_desc, orig.so_pk, min(orig.sub_prod_pckg_pk) as pk from conx.subscriber_services as orig where orig.so_pk is not null and orig.unit_pk = 54 and orig.service_cat_desc = &&Services&s group by orig.unit_pk, orig.service_cat_desc, orig.so_pk order by orig.unit_pk, orig.service_cat_desc, pk ; %end; quit; I think I'm just a few lines away from getting this problem solved. more Message was edited by: SteveONCSU ... View more

Sounds good, I found a good example with your search suggestion. I'll post the code when I'm done. In the meantime, this PDF covers a lot of what I'm doing: http://support.sas.com/resources/papers/proceedings09/061-2009.pdf ... View more

So instead of a single list, I could use something like this, right? SELECT INTO : - : FROM < table> I think this is what Cynthia was talking about when she mentions "numbered list". ... View more

Thanks, I've got the distinct covered in the SQL that loads the temp tables. I think I am exceeding the size of macro variables. My list of unit_pks can have about 70000 integers starting from 1 to 70000. ... View more

Thanks Cynthia, you've been very helpful as always! Using the select into clause is exactly what I was looking for. I was not aware of macro lists either. I think I've got two lists loaded for my two parameters. Now I've just got to figure out how to iterate through them. One question in the meantime: You mentioned a "numbered list of macro variables". Should I be loading the two parameters in any special way?? Here is what I've done to load the lists so far: /**Load service categories into macro variable*/ proc sql noprint; select servicecat into :Services separated by ' ' from ServiceCategories; quit; /**Load units into macro variable*/ proc sql noprint; select unit_pk into :Units separated by ' ' from PossibleUnits; quit; ServiceCategories and PossibleUnits are tables with distinct values of what I want to iterate through. ... View more

orig.unit_pk = 54 and cb_cntr_service_category.service_cat_desc = 'Video' These are the two values I need to iterate through. There are unit_pk's that range from 1 to a big number and service_cat_desc that are about 10 different text values. As you suggest, I could move these parameters to the ON clause to improve performance but this doesn't solve my problem of needing to cycle through different values. I'm thinking use nested loops to iterate through every combination of unit_pk and service_cat_desc. ... View more

This may or may not be the correct forum section for this, but I've read an interesting article in the past about tracking usage using certain log files as source data, then analyzing using whatever SAS component suites the mood. ... View more

What if I could see who the last person was to read this cube using the metadata? ... View more

Thanks for the improvement, I think this will be much more scalable in the future. ... View more

Disregard, I figured it out. I created a new column in the previous data step called ID: ID = _n_; Then here is the updated SQL (changing the 'le' to an actual sign): proc sql; create table BilledUnitsCuSum as select t1.development_pk, t1.year, t1.month, t1.total, sum(t2.total) as CuSum from BilledUnitsFixed as t1 join BilledUnitsFixed as t2 on t2.development_pk = t1.development_pk and t2.ID le t1.ID group by t1.development_pk, t1.year, t1.month, t1.total order by development_pk, year, month ; quit; Stupid less than sign messing up.... Message was edited by: SteveONCSU ... View more