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?
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 */
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 */
Apologies for the late response. We had a long weekend.
I tried your suggested script and it worked! Thank you for your help.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.