DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

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

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?


Accepted Solutions
Solution
‎04-14-2014 06:21 AM
Respected Advisor
Posts: 4,173

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

Posted in reply to 01SASUser

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

View solution in original post


All Replies
Solution
‎04-14-2014 06:21 AM
Respected Advisor
Posts: 4,173

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

Posted in reply to 01SASUser

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

Contributor
Posts: 22

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

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

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

Super User
Super User
Posts: 7,955

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

Posted in reply to 01SASUser

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.

Super User
Posts: 5,429

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

Posted in reply to 01SASUser

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.

Data never sleeps
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 368 views
  • 6 likes
  • 4 in conversation