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

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

01SASUser — Mon, 14 Apr 2014 09:28:59 GMT

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?

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

Patrick — Mon, 14 Apr 2014 10:21:43 GMT

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 */

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

RW9 — Mon, 14 Apr 2014 10:24:09 GMT

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.

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

LinusH — Mon, 14 Apr 2014 10:48:50 GMT

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.

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

01SASUser — Tue, 22 Apr 2014 01:54:42 GMT

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

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