SAS DI (Data Integration): Inserting a Data in an existing Table

Question

I have a program in SAS 9.3 (please refer below) that I need to do in SAS Data Integration Studio:

%macro sqlloop;
  Proc SQL;
   Select distinct(DATE) into :raw_date from RAW;
  Quit;
  %DO k= %sysevalf("&raw_date"d) %TO  %eval(%sysfunc(today())-1);
   PROC SQL;
    insert into CONSOLIDATED (BRANCH_CD, RC_NAME, DATE)
    select BRANCH_CD, RC_NAME, &k.
    from RAW;
   QUIT;
  %END;
%mend;
%sqlloop;

To do this in SAS Data Integration Studio, I did the step and the code inside the "User Written" below:

RAW ------> User Written -----> Table Loader -----> Consolidated

 %let output= &_output;
 %let MySYSLast= &SYSLast;
 %macro sqlloop;
  Proc SQL;
   Select distinct(DATE) into :raw_date from &MySYSLast;
  Quit;
  %DO k= %sysevalf("&raw_date"d) %TO  %eval(%sysfunc(today())-1);
   PROC SQL;
    insert into &output (BRANCH_CD, RC_NAME, DATE)
    select BRANCH_CD, RC_NAME, &k.
    from &MySYSLast;
   QUIT;
  %END;
 %mend;
 %sqlloop;

However, I am receiving an error in running this in SAS DI. May I know how to do this properly in SAS DI?

Please include the error message when you post any question asking for feedback to fix the error. — Keneni, Apr 14 '14 at 15:46

score 0 · Answer 1 · answered Apr 13 '15 at 11:06

0

Why do you require a user written transformation to insert data into existing table when we have load technique -> append to existing available. why to use user written & loader transformation for doing the same work.

answered Apr 13 '15 at 11:06

Kulbhushan Sharma

11
3

score 0 · Answer 2 · answered Oct 26 '15 at 23:25

For what so-ever reason you are using a User written code, which should be avoided in the first place in SAS DI studio, you can re-arrange your code as below to work,

* Remove the 2 %LET statements as DI studio generates code for it already;
%macro sqlloop;

   proc sql noprint;
     /* change the &MYSYSLAST to &_INPUT, this is the first input you connected to the transform */
     select distinct(date) into :raw_date from &_INPUT. ;
   quit;

   %do k= %sysevalf("&raw_date"d) %to  %eval(%sysfunc(today())-1);

      proc sql noprint;
         /* Change the &OUTPUT to &_OUTPUT as that is the standard output for the UW Transform in SAS DI */
         insert into &_OUTPUT. (branch_cd, rc_name, date)
         select branch_cd, rc_name, &k.
         from &_INPUT. ; /* Change &mysyslast to &_INPUT
      quit;

   %end;

%mend sqlloop;

%sqlloop;

NOTE: You can generate code in UW transform by setting code generation mode to "User written Body"
or if you dont want the code to be generated then you can use "All User Written from dropdown

SAS DI (Data Integration): Inserting a Data in an existing Table

2 Answers2