Hi, I have a dataset, and I need to know with code if such a dataset has a primary key constraint defined, and if so which field are there in the primary key. I tried using "describe table constraints" sql-statement, but it just write the result in the log and in a report. Instead, I need to have the result in a dataset or in a macrovariable in order to process the result with code. Can you help me? Thanks a lot [SAS9.4M2] ... View more

Hi, I'm dealing with the following scenario: I have a table RULES which contains two character fiels: CONDITION and DESCRIPTION: RULE CONDITION DESCRIPTION RULE1 COL1 < 0 Warning, COL1 CAN'T BE NEGATIVE (current value = &COL1) RULE2 COL1 + COL2 > 100 Warning, THE SUM COL1+COL2 IS INVALID (current values: &COL1 + &COL2 > 100) Next, I have a table EVENTS which contains some variables: COL1 COL2 COL3 80 40 10 -10 10 0 Note that in RULES.CONDITION I have explicitly the EVENTS columns name; in RULES.DESCRIPTION I have the EVENTS columns name with "&" prefixed. I want to create an OUTPUT table as follows: COL1 COL2 COL3 RULE1 RULE2 RULE1_DESCRIPTION RULE2_DESCRIPTION 80 40 10 0 1 ok Warning, THE SUM COL1+COL2 IS INVALID (current values: 80 + 40 > 100) -10 10 0 1 0 Warning, COL1 CAN'T BE NEGATIVE (current value = -10) ok RULE1/2 are easy (I'm using call execute, I sketch the code so there may be mistakes it is just to point the idea out): data _NULL_;      set RULES;      if (_N_ = 1) then call execute('data OUTPUT; set EVENTS;);      call execute(RULE || '= ifn(' || CONDITION || ',1,0);'); ....... run; This works perfectly. Now I want to add description, so I tried as before: call execute(RULE || '_DESCRIPTION = ifn(' || CONDITION || ',' || DESCRIPTION || ','ok);'); but this does not resolve macrovariables (of course because they don't exists), so I get e.g. Warning, COL1 CAN'T BE NEGATIVE (current value = &COL1). But if I try do define them using symput in the same datastep of course I get wrong values. How would you do that in a simple way? Thanks a lot ... View more

Hi, please check out this code snippet which is driving me crazy: data dataset;   par = 1;   var = 'Valore'; run; %macro mmm(par1);   %put macrovar1=&macrovar;   proc sql;   select var || put(time(),time10.) into :macrovar from dataset where par = &par1;   quit;   %put macrovar2=&macrovar; %mend; data _NULL_;   call execute("%mmm(1)"); run; I expect that macrovar1 puts "not resolved" and macrovar2 puts e.g. "Valore 15:00:00". Instead, I get "not resolved" either in macrovar1 and in macrovar2. Moreover, "macrovar" becomes oddly a global macro variable, hence if I execute again this snippet I get "Valore 15:00:00" either in macrovar1 and in macrovar2, which does not make any sense since I expect macrovar1 to be "not resolved" and macrovar2 to be "Valore 15:01:00" i.e. a new value instead of keeping the old value. However, if I replace the last data step simply with %mmm(1); it works exactly as expected. So what is "call execute" doing wrong? I hope I made myself clear. Thanks a lot Regards ... View more

Hi, I'm writing a program which outputs (at the end) one huge dataset on customers with 350 columns and 20millions rows. During its execution, this program creates: - a "mother table", let's say T1, with ID unique primary key and some fields. These IDs are the whole universe of customers IDs (20millions); so this table is like 10 columns and 20millions rows; - several "child tables", let's say T2....T30, with ID unique primary key and some fields each. Every child table has only a subset of the IDs of T1, varying e.g. from 1% to 99% of the whole universe; I mean that for example T2 can be like 10 columns and 10.000 rows or 10 columns and 19million rows. This is not predictable. Anyway, the primary key ID is always unique and always included in table T1. At the end, these tables must be joined to generate one output dataset: T1 is the "base table", and every T2...T30 is LEFT JOINED with T1 on their unique primary key ID. So: proc sql; CREATE TABLE FINAL AS SELECT T1.ID, T1.FIELD1, ... T1.FIELD10, T2.FIELD1, ... T2.FIELD10, ... ... T30.FIELD1, ... T30.FIELD10 FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID ... LEFT JOIN T30 ON T1.ID = T30.ID ; quit; This works and produces a 15GB table. However, this final proc sql lasts 5 hours! And it creates a temporary sas7butl file in the work directory bigger than 500GB! Things to say: ID is a numeric field with 9 digits all the left joins are one-to-one (ID is unique primary key) every dataset T1,T2,...,T30 is already sorted by ID (before being used in the final proc sql) every dataset T1,T2,...,T30 is indexed on ID using option "_method" on proc sql it shows this: NOTE: SQL execution methods chosen are:       sqxcrta           sqxfil               sqxjm                   sqxsrc( T30 )                   sqxsort                       sqxjm                           sqxsrc( T29 )                           sqxsort                               sqxjm                                   .....                                   sqxsort                                       sqxjm                                           sqxsrc( T2 )                                           sqxsrc( T1 ) Question is: why it appears to be so slow and so resource-consuming? How could it be optimized? Are there any "hints" you may suggest? Thank you very much. ... View more