BookmarkSubscribeRSS Feed
jorquec
Quartz | Level 8

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,

9 REPLIES 9
Tom
Super User Tom
Super User

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
Quartz | Level 8

Sorry , yes current_date is today.

jorquec
Quartz | Level 8

My variable 

month_end_dt

has just a data value 

jorquec
Quartz | Level 8

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
Super User Tom
Super User
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
Super User Tom
Super User

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
Quartz | Level 8

Many thanks for your help.

Reeza
Super User
Run the following to find your SAS version:

proc product_status;run;

Results are in the log. Should be something like:

9.4TS1M5
jorquec
Quartz | Level 8

Many thanks is 9.4 as well.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 746 views
  • 0 likes
  • 3 in conversation