DATA Step, Macro, Functions and more

ERROR 117-185: There was 1 unclosed DO block.

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

ERROR 117-185: There was 1 unclosed DO block.

[ Edited ]

I am writing following code.

 


%let Category_Val =70 79;

data _null_;
array x ( %sysfunc(countw(&Category_Val)) ) _temporary_ (&Category_Val);
do i=1 to dim(x);
putlog x(i)= ;
%U_Table(Cat_ID=x[i]);
end;
run;

 

%u_table code

 


%Macro U_Table(Cat_ID=);

 


/********************************************************************************************************************/
/* Product Details---TMG.CMI_PRODUCT*/
/***** TMG.DS_PRODUCT*****/
/********************************************************************************************************************/

/*Note:
This is to delete table structure and data from TMG table so we can edit and recreate this table*/

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
DROP TABLE TMG.DS_PRODUCT;
)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
CREATE MULTISET TABLE TMG.DS_PRODUCT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
LINE_DESC CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
LINE_NUMBER CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_ID INTEGER NOT NULL,
SH_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DEPARTMENT_ID INTEGER NOT NULL,
SH_DIVISION_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DIVISION_ID INTEGER NOT NULL,
SH_LW_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_LW_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_DEPARTMENT_ID INTEGER NOT NULL,
SH_PRODUCT_BRAND_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_ID INTEGER NOT NULL,
SH_PRODUCT_TYPE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_SD_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_DEPARTMENT_ID INTEGER NOT NULL
)
PRIMARY INDEX ( LINE_NUMBER );

)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
INSERT INTO TMG.DS_PRODUCT
SELECT
LINE_DESC,
LINE_NUMBER,
SH_CATEGORY_DESC,
SH_CATEGORY_ID,
SH_DEPARTMENT_DESC,
SH_DEPARTMENT_ID,
SH_DIVISION_DESC,
SH_DIVISION_ID,
SH_LW_ALT_CATEGORY_DESC,
SH_LW_ALT_CATEGORY_ID,
SH_LW_ALT_DEPARTMENT_DESC,
SH_LW_ALT_DEPARTMENT_ID,
SH_PRODUCT_BRAND_DESC,
SH_PRODUCT_RANGE_DESC,
SH_PRODUCT_RANGE_ID,
SH_PRODUCT_TYPE_DESC,
SH_SD_ALT_CATEGORY_DESC,
SH_SD_ALT_CATEGORY_ID,
SH_SD_ALT_DEPARTMENT_DESC,
SH_SD_ALT_DEPARTMENT_ID

FROM CIS.CMI_PRODUCT
/**** TO REMOVE DUPLICATES FOR A LINE NUMBER. THERE SHOULDN’T HAVE BEEN ANY DUPLICATES IDEALLY, BUT THERE ARE***/
QUALIFY ROW_NUMBER() OVER(PARTITION BY LINE_NUMBER ORDER BY 1)=1
WHERE
SH_CATEGORY_ID IN (&Cat_ID.)
)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;


/**/
/*% U_Table("COOKWARE & KITCHEN");*/

/*************************************Cross join with the Calendar Date******************************************/

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
DROP TABLE TMG.DS_PRODUCT_FINAL;
)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
CREATE MULTISET TABLE TMG.DS_PRODUCT_FINAL ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
LINE_DESC CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
LINE_NUMBER CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_ID INTEGER NOT NULL,
SH_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DEPARTMENT_ID INTEGER NOT NULL,
SH_DIVISION_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DIVISION_ID INTEGER NOT NULL,
SH_LW_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_LW_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_DEPARTMENT_ID INTEGER NOT NULL,
SH_PRODUCT_BRAND_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_ID INTEGER NOT NULL,
SH_PRODUCT_TYPE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_SD_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_DEPARTMENT_ID INTEGER NOT NULL,
CALENDAR_DATE DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX ( LINE_NUMBER );

 

)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
INSERT INTO TMG.DS_PRODUCT_FINAL
SELECT
LINE_DESC,
LINE_NUMBER,
SH_CATEGORY_DESC,
SH_CATEGORY_ID,
SH_DEPARTMENT_DESC,
SH_DEPARTMENT_ID,
SH_DIVISION_DESC,
SH_DIVISION_ID,
SH_LW_ALT_CATEGORY_DESC,
SH_LW_ALT_CATEGORY_ID,
SH_LW_ALT_DEPARTMENT_DESC,
SH_LW_ALT_DEPARTMENT_ID,
SH_PRODUCT_BRAND_DESC,
SH_PRODUCT_RANGE_DESC,
SH_PRODUCT_RANGE_ID,
SH_PRODUCT_TYPE_DESC,
SH_SD_ALT_CATEGORY_DESC,
SH_SD_ALT_CATEGORY_ID,
SH_SD_ALT_DEPARTMENT_DESC,
SH_SD_ALT_DEPARTMENT_ID,
B.CALENDAR_DATE
FROM TMG.DS_PRODUCT
CROSS JOIN TMG.DS_REQUESTED_DATE B

)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;


/*%SQUEEZE(TMG.DS_PRODUCT_FINAL,PDATA.DS_PRODUCT_U);*/

data PDATA.DS_PRODUCT_U;
set TMG.DS_PRODUCT_FINAL;
run;

PROC SORT DATA=PDATA.DS_PRODUCT_U;BY LINE_NUMBER CALENDAR_DATE;RUN;

 

/*Note:**************************************************************************************
This is creating the structure for the table Product Details, which consists details like department, category, product
range, desc etc.
Source table: CIS.CMI_PRODUCT
Output Table: TMG.DS_PRODUCT
****************************************************************************************End*/
proc sort data=PDATA.DS_PRODUCT_U;by line_number CALENDAR_DATE;run;

proc sort data=PDATA.DS_WEBSESSION_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_ABANDON_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_DEMAND_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_NETDISPATCH_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_RETURN_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_PRICE_U;by line_number CALENDAR_DATE;run;

proc sort data=PDATA.DS_WARRANTY_ITEM_U;by line_number;run;
proc sort data=PDATA.DS_INSRUANCE_FLAG_U;by line_number;run;
proc sort data=PDATA.DS_PRODUCT_OFFER_U;by line_number;run;
proc sort data=PDATA.DS_PRODUCT_ATTRIBUTE_U;by line_number;run;
proc sort data=PDATA.DS_PRODUCT_REVIEW_U;by line_number;run;
proc sort data=PDATA.DS_ASSOCIATED_PRODUCT_U;by line_number;run;

proc sort data=PDATA.DS_ATTRIBUTE_U;by line_number ;run;
proc sort data=PDATA.DS_PRODUCT_MINERVA_U;by line_number;run;

 

data PDATA.DS_ENDTOEND_1;
merge
PDATA.DS_PRODUCT_U(in=a)

PDATA.DS_WEBSESSION_U
PDATA.DS_ABANDON_U
PDATA.DS_DEMAND_U
PDATA.DS_NETDISPATCH_U
PDATA.DS_RETURN_U
PDATA.DS_PRICE_U;
by line_number CALENDAR_DATE;
if a=1;
run;

/*data DS_ENDTOEND_1_sample;*/
/*set PDATA.DS_ENDTOEND(obs=100);*/
/*where n_view<>.;*/
/*run;*/


data PDATA.DS_ENDTOEND_2;
merge
PDATA.DS_PRODUCT_U(in=a)
PDATA.DS_PRODUCT_OFFER_U
PDATA.DS_WARRANTY_ITEM_U
PDATA.DS_INSRUANCE_FLAG_U
PDATA.DS_PRODUCT_REVIEW_U
PDATA.DS_ASSOCIATED_PRODUCT_U

PDATA.DS_PRODUCT_ATTRIBUTE_U
PDATA.DS_ATTRIBUTE_U;

by LINE_NUMBER;
if a=1;
run;

DATA PDATA.DS_ENDTOEND_ALL;
MERGE
PDATA.DS_PRODUCT_U(IN=A)
PDATA.DS_ENDTOEND_1
PDATA.DS_ENDTOEND_2
;BY LINE_NUMBER;
IF A=1;
RUN;

%Append_Create()

%Mend U_Table;

 

 

I am passing value from Category_Val to my macro U_Table which actually create a table and update it. Macro is working with one parameter very well. Now my requirement is to pass the category value one by one in macro and keep appending the table from macro. My errors are 

 


ERROR 117-185: There was 1 unclosed DO block.


Accepted Solutions
Solution
‎01-30-2018 12:10 PM
Super User
Super User
Posts: 7,860

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Deepankar wrote:

This worked. i don't want to run it separate as in your first example.

 

in the second example if you can confirm that macro is running one by one.

means value for 70 first then

value for 79 second.

 

how the loop is running here for two different value.


Yes.  It will call/execute the macro once for each value of CAT_ID.

 

If the macro uses the parameter value you passed in to create a table with a fixed name without producing any report or other output then calling it twice will just overwrite the first output with the second. However if the output table name is built dynamically from the parameter value passed in then you could call it twice and it would just create two different tables.

 

I did not look at the definition of the macro at all so I do not know if it can (or if it is even reasonable for to be able to ) handle more than one category code at a time.

 

Describe in words what the macro does and what output you want to create.  What is the meaning of the list of codes that you are starting with?  Do they represent separate things you want to do?  Or are they a set of categories that you want to group together somehow?

 

 

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Can't tell from you post, post full code/log so we can see what is going on.  Also, I really would question why you need to pass a parameter into datastep, you create a generic array, with which to call a macro with that element.  Sounds like a total faff just to use some macro code.  A slight change to data structure or programming will make this very simple.

Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

Are you asking me to run this inside a macro like

 %let Category_Val =70 79;

 

%macro abc();
array x ( %sysfunc(countw(&Category_Val)) ) _temporary_ (&Category_Val);
do i=1 to dim(x);
putlog x[i]= ;
%U_Table(Cat_ID=x[i]);
end;
%mend abc;

 

this is also showing error

 

 

array x ( %sysfunc(countw(&Category_Val)) ) _temporary_ (&Category_Val);
_____
180
MPRINT(ABC): array x ( 2 ) _temporary_ (70 79);

ERROR 180-322: Statement is not valid or it is used out of proper order.

Respected Advisor
Posts: 2,660

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Deepankar wrote:

Are you asking me to run this inside a macro like

 


We are asking you to show us the code for the u_table macro.

--
Paige Miller
Respected Advisor
Posts: 3,834

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Deepankar wrote:

I am writing following code.

 


%let Category_Val =70 79;

data _null_;
array x ( %sysfunc(countw(&Category_Val)) ) _temporary_ (&Category_Val);
do i=1 to dim(x);
putlog x(i)= ;
%U_Table(Cat_ID=x[i]);
end;
run;

 

I am passing value from Category_Val to my macro U_Table which actually create a table and update it. Macro is working with one parameter very well. Now my requirement is to pass the category value one by one in macro and keep appending the table from macro. My errors are 

 


ERROR 117-185: There was 1 unclosed DO block.


The problem is caused by the code coming from %U_Table.  

 

27         %let Category_Val =70 79;
28         data _null_;
29            array x ( %sysfunc(countw(&Category_Val)) ) _temporary_ (&Category_Val);
30            do i=1 to dim(x);
31               putlog x(i)= ;
32               /*%U_Table(Cat_ID=x[i]);*/
33               end;
34            run;

x[1]=70
x[2]=79
Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to data_null__

I know, but when I run u_table separate it runs fine.

Super User
Posts: 9,611

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Please post the code of macro %u_table. The ERROR is caused there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to KurtBremser

pasted below

Super User
Super User
Posts: 7,860

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Your macro must generate a data or proc step.  You cannot nest steps. Once SAS sees another data step or proc step starting it knows you are done defining the current step and tries to run it.  Hence the missing END statement.

Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

can you give me an example how to do it.

 

My purpose is to run macro u_table while i want to pass the parameter from an array.

I dont know the array size.it can be anything.

Super User
Posts: 9,611

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

SHOW.THE.CODE.

 

Can you understand it now?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to KurtBremser

code posted below

Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

U_Table Macro

 


%Macro U_Table(Cat_ID=);

 


/********************************************************************************************************************/
/* Product Details---TMG.CMI_PRODUCT*/
/***** TMG.DS_PRODUCT*****/
/********************************************************************************************************************/

/*Note:
This is to delete table structure and data from TMG table so we can edit and recreate this table*/

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
DROP TABLE TMG.DS_PRODUCT;
)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
CREATE MULTISET TABLE TMG.DS_PRODUCT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
LINE_DESC CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
LINE_NUMBER CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_ID INTEGER NOT NULL,
SH_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DEPARTMENT_ID INTEGER NOT NULL,
SH_DIVISION_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DIVISION_ID INTEGER NOT NULL,
SH_LW_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_LW_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_DEPARTMENT_ID INTEGER NOT NULL,
SH_PRODUCT_BRAND_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_ID INTEGER NOT NULL,
SH_PRODUCT_TYPE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_SD_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_DEPARTMENT_ID INTEGER NOT NULL
)
PRIMARY INDEX ( LINE_NUMBER );

)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
INSERT INTO TMG.DS_PRODUCT
SELECT
LINE_DESC,
LINE_NUMBER,
SH_CATEGORY_DESC,
SH_CATEGORY_ID,
SH_DEPARTMENT_DESC,
SH_DEPARTMENT_ID,
SH_DIVISION_DESC,
SH_DIVISION_ID,
SH_LW_ALT_CATEGORY_DESC,
SH_LW_ALT_CATEGORY_ID,
SH_LW_ALT_DEPARTMENT_DESC,
SH_LW_ALT_DEPARTMENT_ID,
SH_PRODUCT_BRAND_DESC,
SH_PRODUCT_RANGE_DESC,
SH_PRODUCT_RANGE_ID,
SH_PRODUCT_TYPE_DESC,
SH_SD_ALT_CATEGORY_DESC,
SH_SD_ALT_CATEGORY_ID,
SH_SD_ALT_DEPARTMENT_DESC,
SH_SD_ALT_DEPARTMENT_ID

FROM CIS.CMI_PRODUCT
/**** TO REMOVE DUPLICATES FOR A LINE NUMBER. THERE SHOULDN’T HAVE BEEN ANY DUPLICATES IDEALLY, BUT THERE ARE***/
QUALIFY ROW_NUMBER() OVER(PARTITION BY LINE_NUMBER ORDER BY 1)=1
WHERE
SH_CATEGORY_ID IN (&Cat_ID.)
)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;


/**/
/*% U_Table("COOKWARE & KITCHEN");*/

/*************************************Cross join with the Calendar Date******************************************/

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
DROP TABLE TMG.DS_PRODUCT_FINAL;
)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
CREATE MULTISET TABLE TMG.DS_PRODUCT_FINAL ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
LINE_DESC CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
LINE_NUMBER CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_CATEGORY_ID INTEGER NOT NULL,
SH_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DEPARTMENT_ID INTEGER NOT NULL,
SH_DIVISION_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_DIVISION_ID INTEGER NOT NULL,
SH_LW_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_LW_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_LW_ALT_DEPARTMENT_ID INTEGER NOT NULL,
SH_PRODUCT_BRAND_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_PRODUCT_RANGE_ID INTEGER NOT NULL,
SH_PRODUCT_TYPE_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_CATEGORY_ID INTEGER NOT NULL,
SH_SD_ALT_DEPARTMENT_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SH_SD_ALT_DEPARTMENT_ID INTEGER NOT NULL,
CALENDAR_DATE DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX ( LINE_NUMBER );

 

)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;

PROC SQL;
CONNECT TO TERADATA
( OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="TERADATAAUTH" MODE=TERADATA);
EXECUTE
(
INSERT INTO TMG.DS_PRODUCT_FINAL
SELECT
LINE_DESC,
LINE_NUMBER,
SH_CATEGORY_DESC,
SH_CATEGORY_ID,
SH_DEPARTMENT_DESC,
SH_DEPARTMENT_ID,
SH_DIVISION_DESC,
SH_DIVISION_ID,
SH_LW_ALT_CATEGORY_DESC,
SH_LW_ALT_CATEGORY_ID,
SH_LW_ALT_DEPARTMENT_DESC,
SH_LW_ALT_DEPARTMENT_ID,
SH_PRODUCT_BRAND_DESC,
SH_PRODUCT_RANGE_DESC,
SH_PRODUCT_RANGE_ID,
SH_PRODUCT_TYPE_DESC,
SH_SD_ALT_CATEGORY_DESC,
SH_SD_ALT_CATEGORY_ID,
SH_SD_ALT_DEPARTMENT_DESC,
SH_SD_ALT_DEPARTMENT_ID,
B.CALENDAR_DATE
FROM TMG.DS_PRODUCT
CROSS JOIN TMG.DS_REQUESTED_DATE B

)
BY TERADATA;
DISCONNECT FROM TERADATA;
QUIT;


/*%SQUEEZE(TMG.DS_PRODUCT_FINAL,PDATA.DS_PRODUCT_U);*/

data PDATA.DS_PRODUCT_U;
set TMG.DS_PRODUCT_FINAL;
run;

PROC SORT DATA=PDATA.DS_PRODUCT_U;BY LINE_NUMBER CALENDAR_DATE;RUN;

 

/*Note:**************************************************************************************
This is creating the structure for the table Product Details, which consists details like department, category, product
range, desc etc.
Source table: CIS.CMI_PRODUCT
Output Table: TMG.DS_PRODUCT
****************************************************************************************End*/
proc sort data=PDATA.DS_PRODUCT_U;by line_number CALENDAR_DATE;run;

proc sort data=PDATA.DS_WEBSESSION_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_ABANDON_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_DEMAND_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_NETDISPATCH_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_RETURN_U;by line_number CALENDAR_DATE;run;
proc sort data=PDATA.DS_PRICE_U;by line_number CALENDAR_DATE;run;

proc sort data=PDATA.DS_WARRANTY_ITEM_U;by line_number;run;
proc sort data=PDATA.DS_INSRUANCE_FLAG_U;by line_number;run;
proc sort data=PDATA.DS_PRODUCT_OFFER_U;by line_number;run;
proc sort data=PDATA.DS_PRODUCT_ATTRIBUTE_U;by line_number;run;
proc sort data=PDATA.DS_PRODUCT_REVIEW_U;by line_number;run;
proc sort data=PDATA.DS_ASSOCIATED_PRODUCT_U;by line_number;run;

proc sort data=PDATA.DS_ATTRIBUTE_U;by line_number ;run;
proc sort data=PDATA.DS_PRODUCT_MINERVA_U;by line_number;run;

 

data PDATA.DS_ENDTOEND_1;
merge
PDATA.DS_PRODUCT_U(in=a)

PDATA.DS_WEBSESSION_U
PDATA.DS_ABANDON_U
PDATA.DS_DEMAND_U
PDATA.DS_NETDISPATCH_U
PDATA.DS_RETURN_U
PDATA.DS_PRICE_U;
by line_number CALENDAR_DATE;
if a=1;
run;

/*data DS_ENDTOEND_1_sample;*/
/*set PDATA.DS_ENDTOEND(obs=100);*/
/*where n_view<>.;*/
/*run;*/


data PDATA.DS_ENDTOEND_2;
merge
PDATA.DS_PRODUCT_U(in=a)
PDATA.DS_PRODUCT_OFFER_U
PDATA.DS_WARRANTY_ITEM_U
PDATA.DS_INSRUANCE_FLAG_U
PDATA.DS_PRODUCT_REVIEW_U
PDATA.DS_ASSOCIATED_PRODUCT_U

PDATA.DS_PRODUCT_ATTRIBUTE_U
PDATA.DS_ATTRIBUTE_U;

by LINE_NUMBER;
if a=1;
run;

DATA PDATA.DS_ENDTOEND_ALL;
MERGE
PDATA.DS_PRODUCT_U(IN=A)
PDATA.DS_ENDTOEND_1
PDATA.DS_ENDTOEND_2
;BY LINE_NUMBER;
IF A=1;
RUN;

/*this is to append the table*/

%Append_Create()

 

%Mend U_Table;

Super User
Posts: 9,611

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to Deepankar

Your macro contains procedure steps of its own, so you cannot use it to create code inside a data step. Contact the author of the macro, so he/she can tell you how to use it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

Re: ERROR 117-185: There was 1 unclosed DO block.

Posted in reply to KurtBremser

I am the author of all macros here Smiley Happy Tell me what to do

☑ This topic is solved.

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

Discussion stats
  • 19 replies
  • 325 views
  • 0 likes
  • 6 in conversation