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

[01SASUser]

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:

%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?

[Patrick · Accepted solution]

If I understand correctly how your source data looks like then you could do something as below:

/* creating source data */
data raw;
  input branch_cd:$3. rc_name:$20. date:date9.;
  format date date9.;
  datalines;
01 some_name 01feb2014
02 some_other_name 01feb2014
;
run;

/* user written code */
data uw_prep(drop=_source_date);
  set raw(rename=(date=_source_date));
  format date date9.;
  do  date=_source_date to today();
    output;
  end;
run;

/* table loader using append loading UW_PREP into  CONSOLIDATED */

[01SASUser]

Apologies for the late response. We had a long weekend.

I tried your suggested script and it worked! Thank you for your help.

[RW9]

Hi,

Firstly, what is the error message you are receiving.  Also, not sure what you are trying to achieve here, maybe some test data and results wanted would help.

[LinusH]

I can't follow the logic.

Please describe the underlying requirement - often when you transfer existing applicaitons, you may want to re-engineer the work. Either because there are better ways to solving the problem, and secondly you need to adapt DI Studio way of thinking, to make it manageable over time.