but what is problem with sort and set statement. That is clean and easiest way to say. if you data is always in that order that your max id is last record then you can do     proc sql; select * from have group by id having othercolumn = max(othercolumn);    ... View more

it should not differ much. the reason I told to make views is that you may need more space in SAS with table. you have lot of space you can very well make tables and then drop them. ... View more

please remove quotes and then try   distribute_on= firstColumn   Please check this link it has various varitions  and above does not work please try the below. I work mostly with teradata that is why I am not well versed with netezza options but one of them should work   https://www.sas.com/partners/directory/ibm/NetezzaDWAppliances-withSAS.pdf   proc sql; create table nzData.test (DBCREATE_TABLE_OPTS='DISTRIBUTE on (ID)' BULKLOAD=YES) as (select * from SASDATA.SASFoo); quit; ... View more

Can you try distribute_on dataset option. Please pick a unique column for distribute_on. An example picked from sas communities is shown below. or use atleast distrubute_on = random. if you do not specify any column  for distrubute_on then datanase will pick a column and can lead to data skewing and probably making everything inefficient and leading to disconnect     proc sql; create table netlib.customtab(DISTRIBUTE_ON='partno') as select partno, customer, orderdat from saslib.orders; quit; /* for multiple columns*/ data netlib.mytab(DISTRIBUTE_ON='col1,col2'); col1=1;col2=12345;col4='mytest';col5=98.45; run; data netlib.foo(distribute_on=random); mycol1=1;mycol2='test'; run;   ... View more

i will use month and intnx functions to solve this. something like below   data have; date = '09Oct2018'D; output; date = '09Oct2018'D; output; date = '09SEP2018'D; output; date = '09AUG2018'D; output; date = '05AUG2018'D; output; date = '09JUN2018'D; output; format date date9.;; run; data want; set have; where month(date) le month(today()) and month(date) ge month(intnx('month', today(), -2)) ; run;   ... View more

try using    calculated new_asset_execution_type in your group instead of case when   try this. this case when thing works in every other sql implementation, I have used but somehow not in proc sql   proc sql; create table cars as select case when make = 'Acura' then 'Audi' else make end as newmake, model, sum(msrp) from sashelp.cars group by calculated newmake , model ; proc sql; create table cars as select case when make = 'Acura' then 'Audi' else make end as newmake, model, sum(msrp) from sashelp.cars group by case when make = 'Acura' then 'Audi' else make end , model ; ... View more

sure. I worked  on code to process close to 200 millions records with close to 3000 lines of SAS code to Teradata sql, difference was from 20 min to 18 hours. major time issue is because of  I/0  ... View more

@novinosrin I love your answers and learn from you, i agree that  Data step gives more flexibility in terms of Data processing  but   1. Explicit sql has significant impact in run times and they are very important when they are lots of records. 2. Other than Proc sql , no other SQL implementation(as far as I know) allow remerge but multiple passes   ... View more

@Reeza is awesome in helping out ... View more