topic Re: Trying to run a query off another query in SAS Programming

Trying to run a query off another query

wheddingsjr — Tue, 27 Aug 2019 19:41:39 GMT

Hi all

I am trying to run a query off another query. Here is an example without attaching the VERY long query. I ran a query using this criteria:

/** MACROS **/

%LET RANGEBEG = '2019-01-01';

%LET RANGEEND = '2019-07-31';

%LET PAIDRANGE = '2019-07-31';

%LET CODE = svc.BILL_SVC_UNIT_1_CD IN ('92002','92004','92012','92014');

%LET CODE2 = svc.BILL_SVC_UNIT_2_CD IN ('0920');

%LET PAID = svc.CL_SVC_INTL_MSG_1_DSC LIKE 'PAY%';

/**END MACROS **/

PROC SQL;

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

create table First AS

select * from connection to td

(

SELECT MEMNO

,DATE

,DATA

FROM (My Database)

WHERE (MY INFO)

);

quit;

data Final;

set First;

**** THIS SET CREATES THE COMBO COLUMN ****;

COMBO = trim(left(MEMNO))||trim(left(BEGDATE));

PROC SQL;

CREATE Table Results as

SELECT Distinct*

FROM First

QUIT;

I got the data that I wanted, now what I wanted to do is get a list of all the distinct COMBO's in the data so I ran this:

PROC SQL;

CREATE Table Results2 as

Select Distinct COMBO

From RESULTS;

QUIT;

This gave me the list of distinct COMBO’s. Now I want to go back into the database and pull all other lines that uses that same COMBO that DOES NOT include whats in the macros from above, but the column COMBO doesn’t exist in the database. I hope this makes sense and any help on this would be greatly appreciated.

Thanks

Re: Trying to run a query off another query

PaigeMiller — Tue, 27 Aug 2019 20:09:31 GMT

data Final;
set First;
**** THIS SET CREATES THE COMBO COLUMN ****;
COMBO = trim(left(MEMNO))||trim(left(BEGDATE));

There is no BEGDATE in data set FIRST. Nevertheless, you could create this COMBO variable in your PROC SQL something like this:

select memno,
begdate,
cats(memno,begdate) as combo

Moving on

This gave me the list of distinct COMBO’s. Now I want to go back into the database and pull all other lines that uses that same COMBO that DOES NOT include whats in the macros from above, but the column COMBO doesn’t exist in the database. I hope this makes sense and any help on this would be greatly appreciated.

You could still do joins/queries in the database on cats(memno,begdate) even if combo is not in the database.

it would sure be nice if you gave us some input and output so we could see what you want to do. The phrase "DOES NOT include whats in the macros from above" is hard to decipher, as nowhere have you used the macro variables in any of your code and its not obvious how you want to use the macro variables.

Also, for clarity, you do not have macros, you have macro variables.

Re: Trying to run a query off another query

wheddingsjr — Tue, 27 Aug 2019 20:16:59 GMT

Thanks Paige

It was my intent not to bog everyone down with unnecessary minutiae just the bare facts. Also, memno is in the data set First, Begdate is not I just used Date, my bad. The input is Hippa confidential and i did not want to include it. The point is, I got a return of over 750 distinct begdates and of course I could just use them in my macro VARIABLE (I am not all that savvy with the language, thats what I am here for, for answers from kind and knowledgeable people, not those who get a kick out of berating those not as knowledgeable as themselves).

The bottom line is, I wanted to use the list of COMBO's to run against the database to retrieve data without having to insert all 750 distinct combo's. I hope that clarifies for anyone that able to assist.

Thanks

Re: Trying to run a query off another query

PaigeMiller — Tue, 27 Aug 2019 20:21:57 GMT

Pseudo-code

proc sql;
     create table something as select somevariables from database
          where cats(memno,begdate) in (select distinct combo from results);
quit;

Re: Trying to run a query off another query

wheddingsjr — Tue, 27 Aug 2019 21:23:14 GMT

Thanks again Paige. What I did instead was just ran the query and as previously stated ran the code to create the 750 distinct COMBO table. I then ran the entire query again, this time removing the below macro variables (unfortunately for me that garnered 7.8 million lines).

%LET CODE = svc.BILL_SVC_UNIT_1_CD IN ('92002','92004','92012','92014');

%LET CODE2 = svc.BILL_SVC_UNIT_2_CD IN ('0920');

I then just ran the following code to get my final results.

PROC SQL;

create table New as

SELECT *

FROM 2nd Query run

WHERE COMBO IN (select COMBO from DISTINCT COMBO TABLE);

QUIT;

I’m sure there was an easier way to do that, but I got my results. My main original question was how do I use the table I created that showed all 750 distinct combo’s and how to use that list to get all the other like combos from the database. Apparently, it was my lack of SAS terminology etc. that made you not quite understand what I was asking. Thanks nonetheless, your suggestions did point me in a direction where I was able to get the information I needed.

Re: Trying to run a query off another query

ballardw — Tue, 27 Aug 2019 23:26:20 GMT

@wheddingsjr wrote:

Thanks again Paige. What I did instead was just ran the query and as previously stated ran the code to create the 750 distinct COMBO table. I then ran the entire query again, this time removing the below macro variables (unfortunately for me that garnered 7.8 million lines).

%LET CODE = svc.BILL_SVC_UNIT_1_CD IN ('92002','92004','92012','92014');

%LET CODE2 = svc.BILL_SVC_UNIT_2_CD IN ('0920');

I then just ran the following code to get my final results.

PROC SQL;

create table New as

SELECT *

FROM 2nd Query run

WHERE COMBO IN (select COMBO from DISTINCT COMBO TABLE);

QUIT;

I’m sure there was an easier way to do that, but I got my results. My main original question was how do I use the table I created that showed all 750 distinct combo’s and how to use that list to get all the other like combos from the database. Apparently, it was my lack of SAS terminology etc. that made you not quite understand what I was asking. Thanks nonetheless, your suggestions did point me in a direction where I was able to get the information I needed.

That sounds more like a JOIN might be appropriate:

Very generic code:

proc sql; 
   create table combined as 
   select <list the b.variables here>
   from uniquedata as a
        left join
        someotherdata as b
        on a.keyvar1=b.keyvar1
        and a.keyvar2=b.keyvar2
   ;
quit;

which matches all of the key values (assumes 2 variables with same names in the sets but the names need not be the same, just the values) in the unique data set with the matches. The A and B are aliases to differentiate between which data set a variable comes from.

In this example if you have a unique combo of a.keyvar1= ABC and a.keyvar2 = 456 then ALL of the records in the B data set with those values are returned. List the variables you want such as b.var1, b.var3, b.varx from the b set. If there are values in the A set that you want that aren't in B then add them : Select b.var1, b.var3, b.varx , a.somevar, a.othervar

Left join would have a record for each combination of the A set keyvars including those without matches, other types of joins such as Intersect , would have different results.