topic Re: Help with Date Variables in SAS Programming

Help with Date Variables

CTurley — Mon, 14 May 2018 19:00:45 GMT

I have the following code that requests data from an API.

Note in the url, I have to hardcode the begin and end date. I want to use variables here. I want the begin date to be the first day of the current quarter and the end date to be todays date - 1. How do I do this in the code?

Version says SAS EG 7.13 HF5

filename criteo '/opt/sas/data/sasuser/sn161735/criteo.json';

proc http
url="https://publishers.criteo.com/api/2.0/stats.json?apitoken=XXX&begindate=2018-04-01&enddate=2018-04-25&metrics=Date;TotalImpression;CPM;Revenue;WinRate"
method="GET" out=criteo
webusername="cturley@scrippsnetworks.com"
webpassword="kn0xv1lle"
AUTH_BASIC;
run;

libname criteo json fileref=criteo;

PROC SQL;
CREATE TABLE WORK.CRITEO_DATA AS
SELECT t1.date,
t1.totalImpression FORMAT=COMMA9. AS totalImpression,
t2.value FORMAT=DOLLAR9.2 AS Revenue,
t3.value FORMAT=DOLLAR6.2 AS CPM
FROM CRITEO.ROOT t1, CRITEO.REVENUE t2, CRITEO.CPM t3
WHERE (t1.ordinal_root = t2.ordinal_root AND t1.ordinal_root = t3.ordinal_root);
QUIT;

Re: Help with Date Variables

Reeza — Mon, 14 May 2018 19:15:14 GMT

Create macro variables is the easiest way.

You can use INTNX to get the variables and then increment as needed.

Here's an example to create the macro variable for the start, and then you would use it as as follows. You may have issues with your URL and the & signs in it, which will be assumed to be macro variables.

%let month=%sysfunc(intnx(month, %sysfunc(today()), -1, b), yymmddd10.);
%put &month;

url = ".......&begindate=&month.";

@CTurley wrote:

I have the following code that requests data from an API.

Note in the url, I have to hardcode the begin and end date. I want to use variables here. I want the begin date to be the first day of the current quarter and the end date to be todays date - 1. How do I do this in the code?

Version says SAS EG 7.13 HF5

filename criteo '/opt/sas/data/sasuser/sn161735/criteo.json';

proc http
url="https://publishers.criteo.com/api/2.0/stats.json?apitoken=XXX&begindate=2018-04-01&enddate=2018-04-25&metrics=Date;TotalImpression;CPM;Revenue;WinRate"
method="GET" out=criteo
webusername="cturley@scrippsnetworks.com"
webpassword="kn0xv1lle"
AUTH_BASIC;
run;

libname criteo json fileref=criteo;

PROC SQL;
CREATE TABLE WORK.CRITEO_DATA AS
SELECT t1.date,
t1.totalImpression FORMAT=COMMA9. AS totalImpression,
t2.value FORMAT=DOLLAR9.2 AS Revenue,
t3.value FORMAT=DOLLAR6.2 AS CPM
FROM CRITEO.ROOT t1, CRITEO.REVENUE t2, CRITEO.CPM t3
WHERE (t1.ordinal_root = t2.ordinal_root AND t1.ordinal_root = t3.ordinal_root);
QUIT;

Re: Help with Date Variables

Tom — Mon, 14 May 2018 20:40:03 GMT

It is probably easier to generate into a macro variable using a data step and avoid having to work with dates in macro code.

You can use single quotes around the URL in the macro variable to prevent SAS from trying to convert find macro variables named BEGINDATE, ENDDATE or METRICS.

Something like this.

data _null_;
  enddate=today();
  begindate=intnx('qtr',enddate,0,'b');
  token='XXX';
  length url $300 ;
  url=quote(cats('https://publishers.criteo.com/api/2.0/stats.json?apitoken=',token
   ,'&begindate=',put(begindate,yymmddd10.)
   ,'&enddate=',put(enddate,yymmddd10.)
   ,'&metrics=Date;TotalImpression;CPM;Revenue;WinRate'
   ),"'");
  call symputx('url',url);
  call symputx('webusername',quote("YYYYYY"));
  call symputx('webpassword',quote("ZZZZZZ"));
run;

proc http url=&url method="GET" out=criteo
  webusername=&webusername
  webpassword=&webpassword
  AUTH_BASIC
;
run;

Re: Help with Date Variables

Astounding — Mon, 14 May 2018 20:46:35 GMT

Even with hard-coded values, this program has issues.

You have double-quotes around the URL. So how do you prevent &METRIC from being interpreted as a reference to the macro variable METRIC?

What if you were to run the program on the first day of the quarter? Then the begin date would come after the end date.

These are issues you can code around, beginning with the tools that @Reeza mentioned. The formulas might need to be adjusted, but that can be done:

%let begin = %sysfunc(intnx(quarter, "&sysdate9"d, 0), yymmddd10.);

%let end = %sysfunc(putn("&sysdate9"d - 1, yymmddd10.));

But what about the other questions that I raised?

Re: Help with Date Variables

CTurley — Tue, 15 May 2018 15:24:16 GMT

This has me close. When I run this code, I get the data for the first day of the quarter only. (4-1-2018). I thought it was due to having the last day being today. My API's do not like that. I changed the endate parameter by adding -1. I have the code blow. I also tried to make the url length higher but that had no effect either. Any idea why I only get one days data?

filename criteo '/opt/sas/data/sasuser/sn161735/criteo.json';

data _null_;
enddate=today()-1;
begindate=intnx('qtr',enddate,0,'b');
token='xxx';
length url $1300 ;
url=quote(cats('https://publishers.criteo.com/api/2.0/stats.json?apitoken=',token
,'&begindate=',put(begindate,yymmddd10.)
,'&enddate=',put(enddate,yymmddd10.)
,'&metrics=Date;TotalImpression;CPM;Revenue;WinRate'
),"'");
call symputx('url',url);
call symputx('webusername',quote("cturley@sxx"));
call symputx('webpassword',quote("xxx"));
run;

proc http url=&url method="GET" out=criteo
webusername=&webusername
webpassword=&webpassword
AUTH_BASIC
;
run;

PROC SQL;
CREATE TABLE CRITEO_DATA AS
SELECT t1.date,
t1.totalImpression FORMAT=COMMA9. AS totalImpression,
t2.value FORMAT=DOLLAR9.2 AS Revenue,
t3.value FORMAT=DOLLAR6.2 AS CPM
FROM CRITEO.ROOT t1, CRITEO.REVENUE t2, CRITEO.CPM t3
WHERE (t1.ordinal_root = t2.ordinal_root AND t1.ordinal_root = t3.ordinal_root);
QUIT;

Re: Help with Date Variables

Tom — Tue, 15 May 2018 15:58:47 GMT

You could add a PUT statement to the data _null_ to dump the URL that you generated to make sure it is formatted as expected.

You could also look at the resulting JSON file and make sure that the return is what you expected.

How are you converting the JSON output into SAS dataset? Perhaps that step is not working now?