BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ljmrtn
Calcite | Level 5

Hi All

 

I'm a bit new to the world of SAS so please excuse any incorrect terms

 

I'm trying to create a macro that will determine an upper and lower variance of date and time for use in a where statement later on.

 

Input example - 26APR2018:14:30:10 (supposed start time of a call)

 

Output want 1 - 26APR2018:14:30:05

Output want 2 - 26APR2018:14:30:15

 

This would then be used in the same way for an end time and query my data as follows:

 

where StartTime >= 'Output want 1'

and StartTime <= 'Output want 2'

and EndTime >= 'Output want 3'

and Endtime <= 'Output want 3'

 

 

If further context is needed or if there is a better way of doing this then please let me know.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

I made up a quick program with some test data in it:

data have;
input (callstarttime callendtime) (:datetime19.);
format callstarttime callendtime datetime19.;
cards;
25may2018:14:04:10 25may2018:14:10:35
25may2018:14:05:26 25may2018:14:11:40
;
run;

data _null_;
start = "&range_min"dt;
end = "&range_max"dt;
call symput('start1',put(start-5,best.));
call symput('start2',put(start+5,best.));
call symput('end1',put(end-5,best.));
call symput('end2',put(end+5,best.));
run;

proc sql;
create table want as
select * from have
where &start1. <= callstarttime  <= &Start2.
and &end1. <= callendtime <= &end2. ;
quit;

I then defined user prompt of type time range with the name "datetime range" and assigned it to the program.

After entering relevant from and to values (see the following log), the proc sql selected one of the two observations:

10         %LET Range_max_label = Mai 25, 2018 02:10:32 ;
11         %LET Range_max = 25May2018 14:10:32;
12         %LET Range_min = 25May2018 14:04:07;
13         %LET Range_min_label = Mai 25, 2018 02:04:07 ;
14         
15         ODS _ALL_ CLOSE;
16         OPTIONS DEV=ACTIVEX;
17         GOPTIONS XPIXELS=0 YPIXELS=0;
18         ODS LISTING GPATH=&sasworklocation;
19         FILENAME EGSR TEMP;
20         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
21             STYLE=Default
22             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/Default.css")
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26             ENCODING=UTF8
27             options(rolap="on")
28         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
29         
30         GOPTIONS ACCESSIBLE;
31         data have;
32         input (callstarttime callendtime) (:datetime19.);
33         format callstarttime callendtime datetime19.;
34         cards;

NOTE: The data set WORK.HAVE has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.09 seconds
      cpu time            0.00 seconds
      
37         ;

38         run;
39         
40         data _null_;
41         start = "&range_min"dt;
42         end = "&range_max"dt;
43         call symput('start1',put(start-5,best.));
44         call symput('start2',put(start+5,best.));
45         call symput('end1',put(end-5,best.));
46         call symput('end2',put(end+5,best.));
47         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
48         
49         proc sql;
50         create table want as
51         select * from have
52         where &start1. <= callstarttime  <= &Start2.
53         and &end1. <= callendtime <= &end2. ;
NOTE: Table WORK.WANT created, with 1 rows and 2 columns.

54         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.00 seconds

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

See this short example:

data _null_;
start = '26APR2018:14:30:05'dt;
end = ' 26APR2018:14:30:15'dt;
call symput('start',put(start,best.));
call symput('end',put(end,best.));
run;

data have;
format mydt datetime19.;
mydt = '26APR2018:14:30:10'dt;
run;

data want;
set have;
where &start <= mydt <= &end;
run;
ljmrtn
Calcite | Level 5

Thanks Kurt, but i think i may have explained my need poorly. Let me try again.

 

At the start of my code I only want to input two times:

Call start time - '26APR2018:14:30:10'dt

Call end time - '26APR2018:14:45:09'dt

 

These should be be auto calculated for use in my query as follows (notice the 5 second variance from my given times above):

Start query 1 = '26APR2018:14:30:05'dt

Start query 2 = '26APR2018:14:30:15'dt

End query 1 = '26APR2018:14:45:04'dt

End query 2 = '26APR2018:14:45:14'dt

 

These would then be in a query as such (text in bold represent the column names in my data)

proc sql;

create table want as

select * from have

where callstartime >= &Start Query 1.

and callstarttime <= &Start Query 2.

and callendtime >= &End Query 1.

and callendtime <= &End Query 2. ;

quit;

 

The reason behind this methodology is due to my two data sets, PhoneLogA and PhoneLogB, containing information about the same calls but for some reason the start and end times vary between them.

As such, if someone hands me call details of one phonecall from LogA and asks me to find extra info on that call from LogB I want to be able to input the exact details of LogA, and for my code to auto calculate the variance and search between these values in LogB.

 

Annoyingly there is no unique identifier between the two to make life easy so the above is needed.

Kurt_Bremser
Super User

I made up a quick program with some test data in it:

data have;
input (callstarttime callendtime) (:datetime19.);
format callstarttime callendtime datetime19.;
cards;
25may2018:14:04:10 25may2018:14:10:35
25may2018:14:05:26 25may2018:14:11:40
;
run;

data _null_;
start = "&range_min"dt;
end = "&range_max"dt;
call symput('start1',put(start-5,best.));
call symput('start2',put(start+5,best.));
call symput('end1',put(end-5,best.));
call symput('end2',put(end+5,best.));
run;

proc sql;
create table want as
select * from have
where &start1. <= callstarttime  <= &Start2.
and &end1. <= callendtime <= &end2. ;
quit;

I then defined user prompt of type time range with the name "datetime range" and assigned it to the program.

After entering relevant from and to values (see the following log), the proc sql selected one of the two observations:

10         %LET Range_max_label = Mai 25, 2018 02:10:32 ;
11         %LET Range_max = 25May2018 14:10:32;
12         %LET Range_min = 25May2018 14:04:07;
13         %LET Range_min_label = Mai 25, 2018 02:04:07 ;
14         
15         ODS _ALL_ CLOSE;
16         OPTIONS DEV=ACTIVEX;
17         GOPTIONS XPIXELS=0 YPIXELS=0;
18         ODS LISTING GPATH=&sasworklocation;
19         FILENAME EGSR TEMP;
20         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
21             STYLE=Default
22             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/Default.css")
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26             ENCODING=UTF8
27             options(rolap="on")
28         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
29         
30         GOPTIONS ACCESSIBLE;
31         data have;
32         input (callstarttime callendtime) (:datetime19.);
33         format callstarttime callendtime datetime19.;
34         cards;

NOTE: The data set WORK.HAVE has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.09 seconds
      cpu time            0.00 seconds
      
37         ;

38         run;
39         
40         data _null_;
41         start = "&range_min"dt;
42         end = "&range_max"dt;
43         call symput('start1',put(start-5,best.));
44         call symput('start2',put(start+5,best.));
45         call symput('end1',put(end-5,best.));
46         call symput('end2',put(end+5,best.));
47         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
48         
49         proc sql;
50         create table want as
51         select * from have
52         where &start1. <= callstarttime  <= &Start2.
53         and &end1. <= callendtime <= &end2. ;
NOTE: Table WORK.WANT created, with 1 rows and 2 columns.

54         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.00 seconds
ljmrtn
Calcite | Level 5

This has worked perfectly, thank you so much!!!

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!

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
  • 4 replies
  • 696 views
  • 0 likes
  • 2 in conversation