BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
01SASUser
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

4 REPLIES 4
Patrick
Opal | Level 21

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
Fluorite | Level 6

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

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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