topic Re: SAS DI in SAS Data Management

SAS DI

JJP1 — Wed, 03 Jul 2019 13:06:08 GMT

Hi,

i need to take backup for SAS dataset in one SAS DI job only on sunday's.i was trying below code but not working.please help or kindly suggest if you have any other best suggestions please.getting error as below.but the below code is not running by checking the condition,it is just running irrespective of condition.please help

21 %if %eval(&day = 01) %then %do;
ERROR: The %IF statement is not valid in open code.
22 data work.&source_table._&date1;
23
24 set &_input1;
25
26 %end;
ERROR: The %END statement is not valid in open code.

%let _input1=sashelp.cars; 
%let source_table=cars;
%let date1 = %sysfunc(putn(%sysfunc(date()), yymmddn8.));
data _null_ ;
call symput('day', put(weekday(today()),z2.));
run;
%if %eval(&day = 01) %then %do; 
data  work.&source_table._&date1;

	set &_input1;
	

run;

Re: SAS DI

PeterClemmensen — Wed, 03 Jul 2019 13:07:06 GMT

What version of SAS do you use? Using %IF-%THEN-%ELSE in SAS programs is valid from SAS 9.4M5

Re: SAS DI

Patrick — Wed, 03 Jul 2019 13:52:51 GMT

@JJP1

Some code along the line of below might do what you're after.

libname bkup (work);
%let _input1=sashelp.cars; 

%macro backup(bkupLib=, bkupWeekDay=);
  %if %upcase(%sysfunc(today(), weekdate9.))=%upcase(&bkupWeekDay) %then
    %do;
      proc datasets lib=&bkupLib nolist nowarn;
        delete &source_table._%sysfunc(date(), yymmddn8.);
        run;
        append 
          base=&bkupLib..&source_table._%sysfunc(date(), yymmddn8.) 
          data=&_input1;
        run;
      quit;
    %end;
%mend;
%backup(bkupLib=bkup, bkupWeekDay=Wednesday)

And with DIS if you implement via a custom transformation then code as below should be very close to what you need.

libname bkup (work);

/* from source table connected to custom transformation */
%let _input0=sashelp.cars; 
/* from target table connected to custome transformation */
%let _output0=bkup.cars;

/* generated from prompt in custom transformation */
%let bkupWeekDay=Wednesday;

/* code in custom transformation */
%macro backup(bkupWeekDay);
  %local _target_lib _target_tbl;
  %let _target_lib=%scan(work.&_output0,-2,.);
  %let _target_tbl=%scan(&_output0,-1,.)_%sysfunc(date(), yymmddn8.);

  %if %upcase(%sysfunc(today(), weekdate9.))=%upcase(&bkupWeekDay) %then
    %do;
      proc datasets lib=&_target_lib nolist nowarn;
        delete &_target_tbl;
        run;
        append 
          base=&_target_lib..&_target_tbl 
          data=&_input0;
        run;
      quit;
    %end;
%mend;
%backup(&bkupWeekDay)

For the output table: Just create a placeholder metadata object which belongs to the backup library and with the root name of the backup table name.

You could alternatively also pass in the root name via a prompt but you still would need to ensure that the backup library gets assigned. That's something you get "for free" if using a placeholder metadata table object.

Re: SAS DI

JJP1 — Thu, 04 Jul 2019 09:31:32 GMT

Re: SAS DI

Patrick — Thu, 04 Jul 2019 09:46:18 GMT

@JJP1

What's your DIS version?

Re: SAS DI

JJP1 — Thu, 04 Jul 2019 09:49:22 GMT

4.4 patrick,i was geting error when i try to open the custom output work table please

Re: SAS DI

Patrick — Thu, 04 Jul 2019 12:33:21 GMT

@JJP1 wrote:
4.4 patrick,i was geting error when i try to open the custom output work table please

I'm unfortunately on a more recent version so can't just share a .spk with you.

You can't open the target table because the name of this table is not what the custom transform generates as output. It's always a bit difficult to define SAS metadata table objects if the name of this object is dynamic (the date part in the name).

One way to get around this is to also always generate a view with the base name which then points to the most recent physical table with a name of basename_date.

Below how you could do this:

Custom Transformation

Source Code

%macro backup(bkupWeekDay);
  %local _target_lib;
  %let _target_lib=%scan(work.&_output0,-2,.);
  %let _target_tbl=%scan(&_output0,-1,.);
  %let _target_tbl=%sysfunc(substrn(&_target_tbl,1,23))_%sysfunc(date(), yymmddn8.);

  data _null_;
    if "%upcase(&_input0)"="%upcase(&_output0)" then
      do;
        put "Source and Target table may not be identical (same libref and table name)";
        put "aborting job... aborting job....";
        abort abend;
      end;
      stop;
  run;

  %if %eval(%sysfunc(today(), weekday.)=%unquote(&bkupWeekDay)) %then
    %do;
      /* backup source table into &_target_lib..&_target_tbl */
      proc datasets lib=&_target_lib nolist nowarn;
        delete &_target_tbl;
        run;
        append 
          base=&_target_lib..&_target_tbl 
          data=&_input0;
        run;
      quit;
      /* create view &_output0 over &target_lib..&target_tbl */
      proc sql;
        create view &_output0 as
          select *
          from &_target_lib..&_target_tbl
        ;
      quit;
    %end;
%mend;
%backup(&Weekday)

Prompt

Inputs/Outputs

DIS Job

Define the target table as VIEW

Give it a physical name not longer than 23 characters

- The custom transformation will use the view name to construct the table name for the backup table as <view name>_<yymmdd>

- The target table view will then point to the most recently created backup table.

I would implement the target tables as permanent tables (=not as green but red/grey table objects)

Also: Make sure you map source to target columns 1:1. This is functionally not necessary for the code to execute correctly but it will support metadata level column impact analysis.

Re: SAS DI

JJP1 — Fri, 05 Jul 2019 07:27:52 GMT

Hi @Patrick ,

Thanks for helping more on this,may i request to kindly help why we are defining all week day's in prompt please.

As i need just to take the backup only on sunday's please.

also kindly help on the use of below condition under data _null statement please

if "%upcase(&_input0)"="%upcase(&_output0)" then

Re: SAS DI

Patrick — Fri, 05 Jul 2019 10:42:14 GMT

@JJP1 wrote:

Hi @Patrick ,

Thanks for helping more on this,may i request to kindly help why we are defining all week day's in prompt please.

As i need just to take the backup only on sunday's please.

also kindly help on the use of below condition under data _null statement please

if "%upcase(&_input0)"="%upcase(&_output0)" then

With a custom transformation potentially being a reusable asset why not define something that works for any day of the week. Doesn't take much extra work but might be useful. If Sunday is your preferred day then just define this day as the default.

The condition is just an extra step as a fail safe in case you give the target table the same name as the source table (under the same library).. You can drop this whole code bit if you want. I anyway realised that the custom transformation generated code (automatically generate delete code for outputs) would execute before this check so it's actually not useful. The most careful way would be: You keep the check, you don't tick check box "automatically generate delete code...", you add some code on your own after the check which deletes the output table (the view) if it exists. Some code as below should do:
proc datasets lib=%scan(work.&_output0,-2,.) nolist noward mt=(data view); delete %scan(&_output0,-1,.);run;quit

Re: SAS DI

JJP1 — Fri, 05 Jul 2019 13:03:06 GMT

Hi @Patrick ,

Iam getting error after running the custom tranformation,please find error screen shots and normal job properties screen shot please he

Re: SAS DI

Patrick — Fri, 05 Jul 2019 16:57:53 GMT

It's a bit hard to debug a DIS job based on some screenshots. But looking at the Warning it appears that macro variable &weekday hasn't been defined.

I've actually tested what I've posted and it works. I just can't share an .spk with you as I'm on DIS 4.903

In the code I've posted &weekday is the parameter you pass into the macro call:

%backup(&Weekday)

&Weekday is the name you must give the prompt for this to work. That's how this macro variable gets created and populated.

I also wrote "Give it a physical name not longer than 23 characters" so a physical name for your output table of weekdaybackup_OUTPUT0 is not a suitable name.

If your source table is under a different libref than your target backup table then you can give your target table (view) the same name like the source table. So here just call it CARS.

Assuming the "job default for temporary libraries" is WORK the SAS process will then create a view WORK.CARS and a physical SAS table WORK.CARS_<yyyymmdd> (like for today: WORK.CARS_20190706) ....and that's why the view name may not be longer than 23 characters. It's used in the code to create the physical backup table with and underscore and date string added - so that's another 9 characters - and a SAS table name may not exceed 32 characters.

Re: SAS DI

AngusLooney — Sun, 07 Jul 2019 22:20:06 GMT

Embracing the fact that we're using DI Studio, what not try a minimal code approach.

Create a job that backs up your dataset.

Then inside your main job, add a User Written Transform with the following code in it:

data &_output.;
format Is_It_Sunday 8.;
Is_It_Sunday = weekday(date());
if Is_It_Sunday = 1 then output;
run;

and use the output of this node to feed a loop node with the "backup job" attached.

Bingo, the back up "sub job" only runs on Sundays. Easily permutated into running all sorts of days of the week.

Or, consider if this isn't a question of scheduling/orchestration, and use LSF and timed triggering of a dedicated backup flow once a week. Is it really the responsibility of a data processing job to do backups?

Re: SAS DI

Patrick — Mon, 08 Jul 2019 07:25:17 GMT

@AngusLooney

I normally consider a custom transformation as a reusable artefact and as such one should put a bit of effort into it to make it robust and performing.

What you propose here is a process which every single time needs to read the whole source data set whether it's a "backup day" or not.

Re: SAS DI

JJP1 — Mon, 08 Jul 2019 08:14:04 GMT

Hi @Patrick ,

Actually i have SAS4.9 DI also installed .but the logic i need to impmenet this under 4.4.would it be possible to provide .spk file please just to compare and debug the code please.so that i can compare what is wrong iam doing under 4.4 please

Actually i have created prompt correctly only please.

Re: SAS DI

Patrick — Mon, 08 Jul 2019 09:59:32 GMT

@JJP1 wrote:

Hi @Patrick ,

Actually i have SAS4.9 DI also installed .but the logic i need to impmenet this under 4.4.would it be possible to provide .spk file please just to compare and debug the code please.so that i can compare what is wrong iam doing under 4.4 please

Actually i have created prompt correctly only please.

Here you go.

The attached .spk contains two objects.

It's a self contained fully executable DIS job which you can import using DIS4.9 into a folder of your choosing. All data created is in WORK.

I've created the package under DIS4.903 and tested that the import with this DIS version works.

As for debugging stuff:

I often copy/paste the DIS generated code to EG for such debugging work, figure out what's not working and where I need to change and then apply the successful changes to DIS.

If things are still not working in DIS then I normally copy the most recent DIS generated code again into EG and reiterate the process.

Re: SAS DI

JJP1 — Mon, 08 Jul 2019 10:40:08 GMT

Thanks @Patrick ,I tried in 4.4 version itself,successfully imported and ran the job with out any errors.

Thanks for kind response ,even though i asked silly & simple things please.