DATA Step, Macro, Functions and more

Macro time variance for later use

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Macro time variance for later use

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.


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 9,867

Re: Macro time variance for later use

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,867

Re: Macro time variance for later use

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Macro time variance for later use

Posted in reply to KurtBremser

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.

Solution
4 weeks ago
Super User
Posts: 9,867

Re: Macro time variance for later use

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Macro time variance for later use

Posted in reply to KurtBremser

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 114 views
  • 0 likes
  • 2 in conversation