Hi all   I am getting "BY variables are not properly" error in data step because the data has a underscore. Can someone help me with a workaround for this?   data test; input area_code $ flag $; datalines; test 1 transaction 1 txnfdh 1 txn_test 1 ; run;   data _null_; set test; by area_code; run;   It is working if I have notsorted option in by statement, but I need this in a sorted form. I have a call execute followed by 'by' in the real code and I need the macros to run in a controlled order.   It makes no difference to have 'test' data set proc sorted or not. The same error comes even if the data is proc sorted. The error exactly shows up before the txn_test record.   ERROR: BY variables are not properly sorted on data set WORK.TEST. area_code=txnfdh flag=1 FIRST.area_code=1 LAST.area_code=1 _ERROR_=1 _N_=3   Edit:   This error occurs in SAS Greenplum to be specific.   data gpdb1.tests; input area_code $ flag $; datalines; test 1 transaction 1 txnfdh 1 txn_iadbo 1 ; run;   proc sort data=gpdb1.tests out=tests; by area_code; run;   data _null_; set tests; by area_code; run;   Here gpdb1 is a greenplum database. I am accessing it using libname statement.    Thanks   I have attached log.   ... View more

Hi all   data _null_; %let dateend=%sysfunc(today(),date9.); %let startdt1=%sysfunc(intnx(month,"&dateend"d,-1,beginning),datetime24.); run;   I am trying to run this and I am getting wierd date for startdt1. I am getting -01JAN1960:05:45:59. I get the same date time stamp however I try to use datetime format. I m unable to figure out why. Why is this happening and what's the fix.   Thanks! ... View more

Hi all   I have the following code that sends email and updates a column (process_flag) to E after the email has been sent.   proc sql noprint; Connect to Greenplm as dbcon (user=xxxxx pw=xxxxx server=xxxxx port=xxxxx database=xxxxxx); SELECT * INTO :email_flg FROM connection to dbcon ( SELECT count(1)::smallint FROM f2_t1 WHERE rflag = 1 AND (process_flag <> 'E') ); disconnect from dbcon; quit; %let email_body = Hello world; %let to = aa@ss.com; %let cc = ab@ss.com %let subject_text = Test; %if &email_flg > 0 %then %do; filename outbox email &to; data _null_; file outbox to=(&to) cc=(&cc) subject=&subject_text ; put &email_body; run; %end;   proc sql; Connect to xxxx as dbcon (user=xxx pw=xxx server=xxxx port=xxxx database=xxxxx); execute ( update f2_t1 set process_flag = 'E' where run_flag = 1) by dbcon ; disconnect from dbcon; quit;   I am updating the process_flag to E once the email for the process has been sent. I create the email_flg to ensure that email doesn't get sent again. But if the process fails after the email step and before the update statement, the email gets sent twice (or multiple times if the failure keeps occuring after the email step). Is there a way where it is possible to email and do the update in single transaction so that sending email fails if the update fails and vice versa? Or is there any other way to accomplish this scenario in SAS?   Thanks! ... View more

Hi all   I have the following code that sends email and updates a column to E after the email has been sent.   proc sql noprint; Connect to Greenplm as dbcon (user=&user pw=&pass server=&HPAHost port=5432 database=aae_data); SELECT * INTO :email_flg FROM connection to dbcon ( SELECT count(1)::smallint FROM f2_t1 WHERE rflag = 1 AND (process_flag <> 'E') ); disconnect from dbcon; quit; %let email_body = Hello world; %let to = aa@ss.com; %let cc = ab@ss.com %let subject_text = Test; %if &email_flg > 0 %then %do; filename outbox email &to; data _null_; file outbox to=(&to) cc=(&cc) subject=&subject_text ; put &email_body; run; %end;   proc sql; Connect to xxxx as dbcon (user=xxx pw=xxx server=xxxx port=xxxx database=aae_data); execute ( update f2_t1 set process_flag = 'E' where run_flag = 1) by dbcon ; disconnect from dbcon; quit;   I am updating the process_flag to E once the email for the process has been sent. I create the email_flg to ensure that email doesn't get sent again. But if the process fails after the email step and before the update statement, the email gets sent twice (or multiple times if the failure keeps occuring after the email step). Is there a way where it is possible to email and do the update in single transaction so that sending email fails if the update fails and vice versa? Or is there any other way to accomplish this scenario in SAS? Thanks! ... View more