From sasCommunity
Jump to: navigation, search

Questions and Suggestions

The following discussions may need to be moved elsewhere. - Cameron (talk) 19:27, 27 February 2015 (CST)

PROC SQL not efficient with OR condition

At 00:50, 25 August 2012 (CST)Sudhirupa asked:


I have a huge table(Main) which contains millions of records and so many variables. This table has a variables Account", "Token" and 'Trans_Type etc. There is an another table(Lookup ) which contains only two variables - "Account" and their corresponding "Tokens". Now I have to make a final table which will be based on below condition- 1) Corresponding Token from Lookup where substr(Account,15,1)= "2" or Corresponding Token from Main table where Trans_Type = "O".

I wrote this code -

proc sql; 
create table Final as 
select a.*, b.* 
from Main as a Lookup as b 
where a.token = b.token | a.Trans_Type = "O" ; 

As Lookup table is index created on both the column and Main table is also indexed on Account, Token, Trans_Type because Main table is huge table and size of the table is around 300gb. So this query eventually taking 5-6 hrs of time and also taking the huge memory.

Actually I need to join the table because going forward I have to remove the account number column and replace this column with the the corresponding Token values from Lookup Table will be added in the Token Column in Main table.

That's why I am first putting Where substr(Account,15,1)="2" in the Lookup table to get the corresponding Token from the Lookup table.

Now I have to join both the table where all the Tokens from Lookup table or those token where Trans_Type ="O" in the Main table. So this will like Full Join not left join because of OR condition. So if we execute the full join on huge table size will be increased. So need to search the alternate method. Please let me know any other way except Hash Join. Because demerit with Hash join is that it takes lot of memory.

Plz let me know any solution.

Mapping sas enterprise miner code node ..

At 01:53, 3 April 2013 (CST)‎ Ssssireno suggested:

Hi, everyone i discovered how to map the enterprise miner node code to a system called falcon (for detecting fraud online) i mean i could implement a sas datamining model expression(neural network) instead of a traditional rule, this is a success case never done before. 100% mexican achievement

Suppressing small counts for confidentiality

At 17:29, 27 February 2015 (CST)‎ Cliff asked:

Here is my task: If any frequency is from 1-10, then suppressed as '*', for confidential reason.

VALUE CELL_VALUE low-10='*';
proc template;
define crosstabs Base.Freq.CrossTabFreqs;
cellvalue frequency;
define frequency;
ods listing close;
ods tagsets.excelxp file='d:\temp\freq.xml' style=sasweb;
ods tagsets.excelxp options(sheet_interval='none' sheet_name='table1');
proc freq data=aggregate_FY1314;
tables PROVIDER_COUNTY*agegroup*race/nocol norow nopercent;
where  PC EQ 'C3AP'; 
ods tagsets.excelxp options(sheet_interval='none' sheet_name='table2 ');
proc freq data=aggregate_FY1314;
tables PROVIDER_COUNTY*agegroup*race/nocol norow nopercent;
where  PC EQ 'C2AP'; 
ods tagsets.excelxp close;

Not working!

Thanks in advance.