Join tables from different libraries in sas sql procedures

[xliu1]

I need to join multiple tables from different libraries in sas. here is the code I use to connect three tables. SID and Prod are two different libraries in SAS. The problem is when I click on run, the software stops working. The software works fine when I run programs that have tables from the same library. I want to make sure the way I join tables are correct. I use different login credentials for these two libraries.

 from SID.SID_Person_Demo P
        inner join SID.SID_Enrollments_All_Vw E on P.UID=E.UID
        left join Prod.Spraddr A on E.OASIS_PIDM=A.SPRADDR_PIDM

[DavePrinsloo]

Are these non SAS tables, I.e in Oracle or some other database? If so, you are doing what is called a cross-sever join. You may consider using pass-through to join the tables in the background database server. In that case, you would need that one of the Oracle schemes be granted access to the other.

[xliu1]

Thanks.

[Reeza]

It's hard to say without seeing the rest of your query. Are you using SQL Pass-through, RSUBMIT, Regular SQL, FEDSQL????

Either way, assuming you've set up the libnames with two libname statements and are using regular PROC SQL, my guess is these are large tables. When SAS is joining these tables behind the scenes, it actually has to go download all the data first. This can be quite cumbersome from a timing perspective so often I'll filter my data sets to just the minimum set of variables and rows, extract them first and then join within SAS. Ultimately it depends on how often your'e doing this and what type of performance you need.

[xliu1]

Thanks for your prompt response. I use regular sql procedure in sas. I get your points. These are very large tables that have 50000 rows. I will try your way of connecting those datasets. Thanks.

[Reeza · Accepted solution]

50,000 rows wouldn't be considered large in SAS tables at all. If you were doing tens of million is when I'd get concerned. If its only 50,000 rows you should be fine.

[xliu1]

proc sql outobs=500;
    create table NPSAS20_Enrollment as
	    select distinct P.UID label='Student ID',
         P.NAME_FIRST label='First Name',
		 P.NAME_MI label='Middle Name',
		 P.NAME_LAST label='Last Name',
		 P.SUFFIX label='Name suffix',
		 P.GENDER label='Sex',
         case when E.Military_Status in ('A','N') then 2
		      when E.Military_Status in ('E','R','V','Y') then 1
			  when E.Military_Status = 'D' then 0
			  when E.Military_Status is null then 0
		      else -1 end label='Veteran or Active Duty Military status',
		 case when E.DUAL_ENROLL_IND='N' then 0
              when E.DUAL_ENROLL_IND in ('O','P','S','V') then 1
              else -1 end as Dual label='Dual enrollment indicator',
		 substr(P.BIRTH_YYYYMM_DT, 5, 2) as MonthofBirth label='Date of birth month',
		 substr(P.BIRTH_YYYYMM_DT, 7, 2) as DateofBirth label='Date of birth day',
		 substr(P.BIRTH_YYYYMM_DT, 1, 4) as YearofBirth label='Date of birth year',
		 case when P.HISPANIC_FLG = 'Y' then 1
		      when P.HISPANIC_FLG = 'N' then 0
			  else -1 end as Ethnicty label='Ethnicity',
         case when P.WHITE_FLG = 'Y' then 1
              when P.WHITE_FLG = 'N' then 0
			  else -1 end as White label='Race:White',
		 case when P.BLACK_AFRICAN_AM_FLG = 'Y' then 1
              when P.BLACK_AFRICAN_AM_FLG = 'N' then 0
			  else -1 end as Black label='Race:Black or African American',
         case when P.ASIAN_FLG = 'Y' then 1
              when P.ASIAN_FLG = 'N' then 0
			  else -1 end as Asian label='Race:Asian',
         case when P.AM_IND_ALASKAN_FLG = 'Y' then 1
              when P.AM_IND_ALASKAN_FLG = 'N' then 0
			  else -1 end as AmericanIndian label='Race:American Indian or Alaska Native',
         case when P.NAT_HAWAII_PAC_IS_FLG = 'Y' then 1
              when P.NAT_HAWAII_PAC_IS_FLG = 'N' then 0
			  else -1 end as NativeHawaiian label='Race:Native Hawaiian or Pacific Islander',
	     case when A.SPRADDR_ATYP_CODE = 'PM' then A.SPRADDR_STREET_LINE1 else '' end as Addressline1 label='Permanent address 1' 

        from SID.SID_Person_Demo P
        inner join SID.SID_Enrollments_All_Vw E on P.UID=E.UID
        left join Prod.Spraddr A on E.OASIS_PIDM=A.SPRADDR_PIDM

Where E.TERMID IN ('201905','201908','202001')
          and E.BENCH='B'; 
               
		
	select * from NPSAS20_Enrollment;
QUIT;

Here are my codes. Three tables are used. The program works fine until I joined Prod.Spraddr into the program.

[Reeza]

case when E.Military_Status in ('A','N') then 2
		      when E.Military_Status in ('E','R','V','Y') then 1
			  when E.Military_Status = 'D' then 0
			  when E.Military_Status is null then 0
		      else -1 end label='Veteran or Active Duty Military status',

Missing an as there?

No variable name?

Also, if you're recoding a bunch of Y/N to a consistent code, ie 0/1/-1 then I would suggest using an informat instead. It's cleaner and you only have to change it in one place. I'd also recommend leaving the -1 as missing, that way you can do summary stats on the column without any issues. If you have multiple missing values, use a special missing.

For testing purposes, I'd split the join and data extract from the new variable creation to ensure that I knew which was taking time and worth making more efficient. My suggestions above are faster for you as a programmer but likely won't affect your run time that significantly except the informat portion. 

[xliu1]

Thanks for your response. I am not very familiar with informat coding. Would you please send me an example?

Also I want to clarify the recommendation you mentioned in your previous response about "extract them first and then join within SAS". Do you mean I create tables using the same library, and then use "data merge" function to combine them later? Thanks for your help!

[Reeza]

Here's a paper that covers informats and their usages:

https://www.lexjansen.com/pharmasug/2005/posters/po06.pdf

For data extracts, I mean I'd pull down parts of each table manually via a sql or data step, filtering out rows and columns as much as possible.

And then do a SQL merge after, you can use SQL on your SAS data sets or a data step merge, they're usually equivalent approaches. 

[xliu1]

Thanks for shared information!

[DavePrinsloo]

On the concept of "extract them first": The simplest way is to use proc copy to copy the entire tables.
You would want to do that with SID.SID_Person_Demo and Prod.Spraddr.
Then use a data step or proc sql to copy the data from SID.SID_Enrollments_All_Vw
with Where TERMID IN ('201905','201908','202001') and BENCH='B';

Since all the data is now in SAS, the join should be fast.

[xliu1]

Thanks for your response!