topic Re: Create a data set after a case when in New SAS User

Create a data set after a case when

jorquec — Mon, 08 Jul 2019 14:36:16 GMT

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,

Re: Create a data set after a case when

Tom — Mon, 08 Jul 2019 14:51:44 GMT

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;

Re: Create a data set after a case when

jorquec — Mon, 08 Jul 2019 15:04:26 GMT

Sorry , yes current_date is today.

Re: Create a data set after a case when

jorquec — Mon, 08 Jul 2019 15:06:43 GMT

My variable

month_end_dt

has just a data value

Re: Create a data set after a case when

jorquec — Mon, 08 Jul 2019 15:56:23 GMT

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;

Re: Create a data set after a case when

Tom — Mon, 08 Jul 2019 16:02:19 GMT

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.

Re: Create a data set after a case when

Tom — Mon, 08 Jul 2019 16:09:01 GMT

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.

Re: Create a data set after a case when

Reeza — Mon, 08 Jul 2019 16:14:10 GMT

Run the following to find your SAS version:

proc product_status;run;

Results are in the log. Should be something like:

9.4TS1M5

Re: Create a data set after a case when

jorquec — Tue, 09 Jul 2019 07:52:00 GMT

Many thanks is 9.4 as well.

Re: Create a data set after a case when

jorquec — Tue, 09 Jul 2019 07:52:30 GMT

Many thanks for your help.