Create a data set after a case when

[jorquec]

Hi ,

Please  I don't have any idea how to write a sas code to create a table after a case when validation, as example I would like to create a table just to be a trigger event. 

For example 

Proc SQL ( i will get some data from table A which is on teradata)

Create a table STATUSPAYG

If this condition is true:  max (A.month_end_dt) = current_date from table A 

then  create a table STATUSPAYG  with a simple unique row = 1

data STATUSPAYG_final;
set STATUSPAYG;

My real objective is create a simple table just saying yes or 1 when the condition max(month_end_dt) = current date is true.

Because I will use this table as a trigger event on SAS management console.

Any any help I really appreciate.

Many thanks,

[Tom]

What is current date? Do you mean the day at the time that step of the program runs, ie the output of the TODAY() function? Or when the SAS session started? "&sysdate9"d 

Also does you variable have a date value or a datetime value?

proc sql noprint;
select max_dt = today()
  into :today trimmed
  from (select max(A.month_end_dt) as max_dt from A)
;
quit;

%if &today %then %do;
data STATUSPAYG_final;
  set STATUSPAYG;
run;
%end;

[jorquec]

Sorry , yes current_date is today.

[jorquec]

My variable 

month_end_dt

has just a data value 

[jorquec]

Hi, 

Many thanks for your help!! Just  a few questions:

I wrote this code but it returns me some errors msg like:

-ERROR: The %IF statement is not valid in open code.

ERROR: The %END statement is not valid in open code.

My SAS version is 7.13

Could you please tell me if I    forgot something.

It created a data set but I don't have a way to see if the table is refreshed so it would be possible to see in the final data set created the max (month_end_dt) found? 

the actual code just show me a value 0 (zero)

%include '/SASCommon/jorquec/credentials.sas';

libname jorquec '//SASCommon/jorquec' ; /* criei esse folder para salvar arquivos*/
libname nuv teradata user= &teradata_user. password = &teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view';

proc sql noprint ;
connect to teradata
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
Create table STATUSPAYG as(
select max_dt = today()
into :today trimmed
from (select max(month_end_dt) as max_dt from nuv.pg_margin_stack)
)
;

quit;

%if &today %then %do;
data jorquec.STATUSPAYG_final;
set STATUSPAYG;
run;
%end;

[Tom]

If you are using an older version of SAS you need to wrap those macro logic statements inside of a macro definition and then run the macro.
Note: there is no way you are running SAS version 7.13. There were not 13 releases of SAS version 7 and I doubt you are running a version of SAS that is over 20 years old.
That must be the version number of the Enterprise Guide program that you are using to submit code to SAS.

[Tom]

If you have an old version of SAS and don't want to create a macro then just put the code you want to run into a macro variable instead.

proc sql noprint;
%let code=;
select 
'data STATUSPAYG_final;
  set STATUSPAYG;
run;'
  into :code
  from (select max(A.month_end_dt) as max_dt from A)
  where max_dt = today()
;
quit;

&code.

[jorquec]

Many thanks for your help.

[Reeza]

Run the following to find your SAS version:

proc product_status;run;

Results are in the log. Should be something like:

9.4TS1M5

[jorquec]

Many thanks is 9.4 as well.