BookmarkSubscribeRSS Feed
wheddingsjr
Pyrite | Level 9

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

6 REPLIES 6
r_behata
Barite | Level 11

 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
Super User

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
Obsidian | Level 7

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_
Rhodochrosite | Level 12

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
Super User

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_
Rhodochrosite | Level 12

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 6 replies
  • 1403 views
  • 0 likes
  • 5 in conversation