Using a Pivot table to use as criteria

[wheddingsjr]

Hi all

I am trying to run a program where I get results of a pivot table and try to use that pivot table to get info in a new query. I keep getting the error message that the Pivot table doesnt exist. I am attaching a sample of the script I was trying to run. Any assistance would be greatly appreciated.

Thanks

[r_behata]

 Hello @wheddingsjr

Its very difficult to follow what your code is doing unless you provide more specifics and the exact error message in the log that you are getting while running this query. With the details you have given all I can understand is that there are 3 stand alone queries which do not have anything to do with each other.

[Reeza]

You're using SQL Pass through in the two SQL queries, this means the output table is stored locally, after it runs on the server. That means you only have access to table on the server when doing a pass through query. The PIVOT table is local though, so it does not exist in the server so you cannot use it in your second query. You need to create the table on the server or nest the whole query together. 

Please post your code directly into the forum, like this:

/** MACROS **/
%LET RANGEBEG = '2017-12-01';
%LET RANGEEND = '2018-11-30';
%LET PAIDRANGE = '2018-11-30';
%LET CODE = svc.APPL_SVC_PV_NBR IN ('706067');
----------------------------------------------------------------------
/**END MACROS **/

PROC SQL;
CONNECT to teradata as td ( tdpid='pg' mode=teradata);

create table firstcut AS
select * from connection to td
  (
SELECT *
FROM DATABASE
WHERE 
     BEGDATE BETWEEN &RANGEBEG AND &RANGEEND
     AND svc.FINAL_ADJUD_DT LE &PAIDRANGE
     AND svc.FINAL_ADJUD_DT IS NOT NULL
     AND &CODE
     AND svc.BUSI_LIN_CD  IN ('COM')
     AND svc.INT_PNLTY_IND <>'Y’
     AND NOT ch.PRC_APPL_CD IN ('UMC','IKA') 	 
     AND svc.ADJUD_IND = 'Y' 
     AND svc.LAST_VER_FULL_ADJUD_LCS_IND ='Y'
); 
quit;
----------------------------------------------------------------------
PROC SQL;
create table PIVOT as
SELECT MEMNO
FROM Results
GROUP BY MEMNO
ORDER BY MEMNO ASC;
QUIT;
----------------------------------------------------------------------
PROC SQL;
CONNECT to teradata as td ( tdpid='pg' mode=teradata);

create table firstcut1 AS
select * from connection to td
  (
SELECT *
FROM DATABASE
WHERE 
     BEGDATE BETWEEN '2017-12-01' AND '2018-11-30'
     AND svc.FINAL_ADJUD_DT LE &PAIDRANGE
     AND svc.FINAL_ADJUD_DT IS NOT NULL
     AND MEMNO IN (select MEMNO from PIVOT)
AND svc.BUSI_LIN_CD  IN ('COM')
AND svc.INT_PNLTY_IND <>'Y'
AND NOT ch.PRC_APPL_CD IN ('UMC','IKA')
 	AND svc.ADJUD_IND = 'Y' 
	AND svc.LAST_VER_FULL_ADJUD_LCS_IND ='Y'
); 
quit;

[johnsville]

you really need only one query to execute inside teradata:

SELECT *
FROM DATABASE
WHERE 
     BEGDATE BETWEEN &RANGEBEG AND &RANGEEND
     AND svc.FINAL_ADJUD_DT LE &PAIDRANGE
     AND svc.FINAL_ADJUD_DT IS NOT NULL
     AND MEMNO IN (SELECT DISTINCT memno
					FROM DATABASE
					WHERE 
					     BEGDATE BETWEEN &RANGEBEG AND &RANGEEND
					     AND svc.FINAL_ADJUD_DT LE &PAIDRANGE
					     AND svc.FINAL_ADJUD_DT IS NOT NULL
					     AND &CODE
					     AND svc.BUSI_LIN_CD  IN ('COM')
					     AND svc.INT_PNLTY_IND <>'Y’
					     AND NOT ch.PRC_APPL_CD IN ('UMC','IKA') 	 
					     AND svc.ADJUD_IND = 'Y' 
					     AND svc.LAST_VER_FULL_ADJUD_LCS_IND ='Y'
					)
AND svc.BUSI_LIN_CD  IN ('COM')
AND svc.INT_PNLTY_IND <>'Y'
AND NOT ch.PRC_APPL_CD IN ('UMC','IKA')
 	AND svc.ADJUD_IND = 'Y' 
	AND svc.LAST_VER_FULL_ADJUD_LCS_IND ='Y'
;

[kiranv_]

you are creating SAS work table and trying to use that in explicit SQL pass through query(via connect statement). Once to connect statement to Teradata, you cannot use SAS work table. For your query, you can use  Teradata volatile table, all users can create Teradata volatile table and use the same query. below link shows how to create volatile table and use them. 

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677096.htm.

or you can do everything in SAS by using teradata libname statement.

     libname teralib teradata server=myserver user=myuserid pwd=mypass;
    ; 

 proc sql;
 create teralib.yourotherteradatatable as
select id, col from teralib.yourteradatatable 
where id in(select id from sastable); 

[Reeza]

Note that when you mix data from a local table and server table, the full server table may be downloaded before it's processed. 

This can really slow things down. 

---

@kiranv_ wrote:

you are creating SAS work table and trying to use that in explicit SQL pass through query(via connect statement). Once to connect statement to Teradata, you cannot use SAS work table. For your query, you can use  Teradata volatile table, all users can create Teradata volatile table and use the same query. below link shows how to create volatile table and use them. 

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677096.htm.

or you can do everything in SAS by using teradata libname statement.

     libname teralib teradata server=myserver user=myuserid pwd=mypass;
    ; 

 proc sql;
 create teralib.yourotherteradatatable as
select id, col from teralib.yourteradatatable 
where id in(select id from sastable); 

---

[kiranv_]

you are right @Reeza, so volatile table is the way to go as mentioned in the first solution.