BookmarkSubscribeRSS Feed
RoyGipson
Calcite | Level 5

Hello SAS Community,

 

I have a scenario where we will need to pull data from multiple databases. Right now we are manually / hard coding through a left join database names that will be created over time. These databases only come into existence once the month end has occurred.

 

i.e. -

 

VCRCM1801 - DB Name VCRCM suffixed with 18 (for 2018), and 01 (for January)

VCRCM1802 - DB Name VCRCM suffixed with 18 (for 2018), and 01 (for February)

VCRCM1803 - DB Name VCRCM suffixed with 18 (for 2018), and 01 (for March)

VCRCM1804 - DB Name VCRCM suffixed with 18 (for 2018), and 01 (for April)

VCRCM1805 - DB Name VCRCM suffixed with 18 (for 2018), and 01 (for May)

 

So as you can see if were running the report in May of 2018 we would only need to left join DBs up to April 2018 (i.e. - VCRCM1804). What we need to do is code so that the DB name checks (the suffixes) is performed ahead of the script leveraging in the joins... so the script doesn't error out as May 2018 DB name (VCRCM1805 ) doesn't exist yet.

 

Can someone tell me how to use this code to perform those dynamic checks to see if the DB(s) exist first?

 

 


%LET OFFSET = -30;

DATA _NULL_;
     CALL SYMPUT('YEAR',PUT(YEAR(INTNX('YEAR',TODAY()&OFFSET.,0)),Z4.));
     CALL SYMPUT('MONTH',PUT(MONTH(INTNX('MONTH',TODAY()&OFFSET.,-0)),Z2.));
     CALL SYMPUT('YY',PUT(TODAY()&OFFSET.,YEAR2.));
     CALL SYMPUT('MM',PUT(MONTH(INTNX('MONTH',TODAY()&OFFSET.,-0)),Z2.));
     CALL SYMPUT('DD',PUT(DAY(INTNX('DAY',TODAY()&OFFSET.,-0)),Z2.));
     CALL SYMPUT('START_DT',CATS("'",PUT(INTNX('MONTH',TODAY()&OFFSET.,-0,'BEGINNING'),DATE9.),"'D"));
     CALL SYMPUT('END_DT',CATS("'",PUT(INTNX('MONTH',TODAY()&OFFSET.,0,'END'),DATE9.),"'D"));
RUN;
      
%LET DBNME  = VPCHM&YY.&MM.;

PROC SQL; SELECT COUNT(*) INTO :EXSTTBL FROM DBC.TABLESV WHERE TABLENAME = 'PTY_RB' AND DATABASENAME = "&DBNME.";

***************************************************
* IF DEPENDENT TABLE DOES NOT EXIST, EXIT THE JOB
***************************************************;

%IF &EXSTTBL. <= 0 %THEN %DO; SOMETHING…..; %END;

 

 

I'm using SAS EG Version 7.12 HF2 (7.100.2.3386) (32-bit)

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please don't code all in uppercase.

Why do you need to left join all these data?  I assume the data within each is exactly the same, and you are trying to create a wide dataset with date in columns.  This is inefficient for a number of reasons.  In this case, if you go for a long dataset - which is easy to program with, takes up less disk space, etc. - then the coding for the task is simple (as will all further code):

libname temp ... connect to oracle syntax here;

data want;
  set temp.vcrcm180: indsname=tmp;
  dsname=tmp;
run;

This will give you a long dataset with all the datasets with the prefix of vcrm180 from the libname connection.  And for each row will take the name of the incoming dataset.  This way you have a nice fixed structure which doesn't change, and rows for the data. The you also have the ability if you really have to (for a report maybe) to transpose the data up using the new variable.  Avoids all macro code.

RoyGipson
Calcite | Level 5

Thanks for the quick response! I need to code for these different databases because there's so much data (transactions) that with the completion of each prior month the data warehouse creates an entirely separate database for each month's activities... So I will always need to connect to the DBs from the beginning of the year to the latest DB (or month) that was created (or completed).

 

I have a starting point, let's say a referral... and going forwards with each report I need to search the databases for any corresponding account opening activity...

 

I will attempt to test your concept / code with a simple approach first... thanks again!

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Are all these databases in one database instance?  Just thinking you could create one view of all the data on the database - your DB person can advise - and then you just need to query one stacked view of the data.  Would make your life a lot easier.

RoyGipson
Calcite | Level 5

They're in one large data warehouse, but as mentioned the databases for future months i.e. - October 2018, November 2018 have not been created as of yet. But I will need to code for those databases in order to automate my process. Even though they currently don't exist at the present time.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

It should not matter.  All database (and SAS) have a thing called metadata.  This is information about the database stored in a dataset itself so that you can use it.  For instance in SAS you have sashelp.vtable and vcolumn, which will tell you what datasets appear in what libraries and what column information they have.   The same can be done in SQL with dictionary.tables and columns.  Within this information you know what is present.  Say for instance I have a load of datasets with dates suffix in library xyz and I want those from 2018 (yymmdd format):

data _null_;
  set sashelp.vtable (where=(libname="XYZ" and index(memname,"2018"))) end=last;
  if _n_=1 then call execute('data want; set ');
  call execute(" "||memname);
  if last then call execute(';run;');
run;

This will from the metadata return all rows which fulfill the criteria of being in XYZ, and having 2018 as part of the dataset name.  From that we use code generation to create a datastep which sets all these together.  

 

So in your database, there will be a table which tells you which databases have been created and you can use that list.

Patrick
Opal | Level 21

@RoyGipson

Given the assumed working code you've posted....

%LET DBNME  = VPCHM&YY.&MM.;
PROC SQL; SELECT COUNT(*) INTO :EXSTTBL FROM DBC.TABLESV WHERE TABLENAME = 'PTY_RB' AND DATABASENAME = "&DBNME.";

....it looks to me that all these databases "live" on a single database server and that you've got in DBC.TABLESV access to a dictionary table which actually got all the data to figure out which databases with which tables in it exist.

Can't you just use this dictionary table as the basis to dynamically generate the code you need?

 

If code generation gets a bit more complex then my personal preference is to use one or multiple DATA _NULL_ steps with put statements where I write the generated code to a temporary file (filename codegen temp; data _null_; file codegen; ....<all the logic>; run) and then use an %include to actually execute this generated code ( %include codegen / source2 ) .

And during development instead of writing to codegen I just print the code (file print;);

 

Does that make sense?

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 933 views
  • 0 likes
  • 3 in conversation