SAS conditional execution of proc sql

Question

I have created a stored process in SAS that prompts the user to select a month/year combination which looks like this 2015_10. Then from the next box they can click on a calendar and select a startdate which is a timestamp and an enddate also a timestamp.

I would like to combine this into one step, where the user only selects the start and end date. However, my source table is in SQL Server, and the tables are partitioned by months, and the tables are named like this datatabel_2015_10 where the last two digits represent the month. Once the user selects the month, I have proc sql query that table, and then after that there is another query to pull only the rows which fall between the start date and end date, those are time1 and time2 stored as character strings in MS SQL Server which look like this 30JAN2015:19:52:29

How can I code this up so as to eliminate the month/year prompt and only have two selections, namely startdatetime and enddatetime, and still get the query. Concatenating the monthly tables is not an option because they are huge and runs forever even if I use a pass through query. Please help. Thanks

LIBNAME SWAPPLI ODBC ACCESS=READONLY read_lock_type=nolock noprompt="driver=SQL server; server=XXX; Trusted Connection=yes; database=XXX" schema='dbo'; 

proc sql; 
  create table a as 
  select 
  startdate_time, 
  enddate_time 
  from SWAPPLI.SQL_DB_2015_10 
quit;

proc sort data=a out=b; 
  by startdate_time, enddate_time 
where enddate_time between "&startdate"dt and "&enddate"dt; 
run;

Please include the relevant parts of your stored process code in the question — mjsqu
LIBNAME SWAPPLI ODBC ACCESS=READONLY read_lock_type=nolock noprompt="driver=SQL server; server=XXX; Trusted Connection=yes; database=XXX" schema='dbo'; proc sql; create table a as select startdate_time, enddate_time from SWAPPLI.SQL_DB_2015_10 quit; proc sort data=a out=b; by startdate_time, enddate_time where enddate_time between "&startdate"dt and "&enddate"dt; run; — user601828

Thogerar Thogerar · Accepted Answer · 2017-10-20T12:03:27

You can delete a step by asking directly the timestamp begin and end.

Then you can deduce the YEAR and MONTH from the timestamp selected.

For &startdate = 30JAN2015:19:52:29, you can create 2 macro-variable by creating 2 substring following this :

%let startdate = 30JAN2015:19:52:29;

%let YEAR=%SUBSTR("&startdate",4,3);
%let MONTH=%SUBSTR("&startdate",7,4);

%PUT YEAR=&YEAR;

%PUT MONTH=&MONTH;

Result :

        %PUT YEAR=&YEAR;
YEAR=JAN

         %PUT MONTH=&MONTH;
MONTH=2015

Then you can create a %if condition to match JAN with 01, FEB with 02, ect... Here MONTH will be 01 So you don't need to ask the information 2 times any more.

Then you can select your dataset by doing this :

proc sql; 
  create table a as 
  select 
  startdate_time, 
  enddate_time 
  from SWAPPLI.SQL_DB_&YEAR._&MONTH. 
quit;

proc sort data=a out=b; 
  by startdate_time, enddate_time 
where enddate_time between "&startdate"dt and "&enddate"dt; 
run;

You should probably restrict the selection to a MONTH and not allow the selection through several month like start=01JAN and end=01MAR. Because the selection will be only on the dataset SQL_DB_2017_01 and not taking into account the end=01MAR.

SAS conditional execution of proc sql

1 Answers