topic read daily json datafiles in SAS Programming

read daily json datafiles

ANLYNG — Fri, 16 Oct 2020 16:09:09 GMT

I need to read daily supplied json files on a dynamic way eg. name of datafile is data_20201016_system.json - the numbers is the date of the current day.

How can I in a simple way load these files ?

I use SAS DI or SAS EG.

I have tried the libname but it fails.

Thanks in advance.

my code is here:

%let dato_d=%sysfunc(putn(%eval(%sysfunc(today())-3),yymmddn8.));
%put &dato_d;

libname IN json '\\DW\Data\data_"&dato_d."_system.json' map='user.map' automap=create;

libname out '\\DW\Dataout';

data out.data_"&dato_d"_system;
set IN.ROOT;
run;

log

4312 libname IN json
4312 ! '\\DW\data\data_"&dato_d."_system.json'
4312 ! map='user.map' automap=create;
ERROR: Invalid physical name.
ERROR: Error in the LIBNAME statement.
4313
4314 libname out '\\DW\Dataout';
NOTE: Libref OUT was successfully assigned as follows:
163 The SAS System 17:12 Friday, October 16, 2020

Engine: V9
Physical Name: \\\\DW\Dataout
4315
4316 data out.data_"&dato_d"_system;
NOTE: Line generated by the macro variable "DATO_D".
4316 "20201013
_________
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.

4317 set IN.ROOT;
ERROR: Libref IN is not assigned.
4318 run;

ERROR: The value '20201013'n is not a valid SAS name.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set OUT.DATA_ may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set OUT.DATA_ was not replaced because this step was stopped.

Re: read daily json datafiles

Reeza — Fri, 16 Oct 2020 16:19:13 GMT


libname IN json "\\DW\Data\data_&dato_d._system.json" map='user.map' automap=create;

Remove the inside double quotes and change the outer ones to double from single.

That should work for you now.

@ANLYNG wrote:

I need to read daily supplied json files on a dynamic way eg. name of datafile is data_20201016_system.json - the numbers is the date of the current day.

How can I in a simple way load these files ?

I use SAS DI or SAS EG.

I have tried the libname but it fails.

Thanks in advance.

my code is here:

%let dato_d=%sysfunc(putn(%eval(%sysfunc(today())-3),yymmddn8.));
%put &dato_d;

libname IN json '\\DW\Data\data_"&dato_d."_system.json' map='user.map' automap=create;

libname out '\\DW\Dataout';

data out.data_"&dato_d"_system;
set IN.ROOT;
run;

log

4312 libname IN json
4312 ! '\\DW\data\data_"&dato_d."_system.json'
4312 ! map='user.map' automap=create;
ERROR: Invalid physical name.
ERROR: Error in the LIBNAME statement.
4313
4314 libname out '\\DW\Dataout';
NOTE: Libref OUT was successfully assigned as follows:
163 The SAS System 17:12 Friday, October 16, 2020

Engine: V9
Physical Name: \\\\DW\Dataout
4315
4316 data out.data_"&dato_d"_system;
NOTE: Line generated by the macro variable "DATO_D".
4316 "20201013
_________
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.

4317 set IN.ROOT;
ERROR: Libref IN is not assigned.
4318 run;

ERROR: The value '20201013'n is not a valid SAS name.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set OUT.DATA_ may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set OUT.DATA_ was not replaced because this step was stopped.

Re: read daily json datafiles

Tom — Fri, 16 Oct 2020 16:44:17 GMT

Just remove the random quotes. Only put the quotes where the SAS syntax needs them.

Also you don't need the %EVAL() as %sysfunc() will evaluate normal SAS expressions.

%let dato_d=%sysfunc(putn(%sysfunc(today())-3,yymmddn8.));
%put &=dato_d;

libname IN json "\\DW\Data\data_&dato_d._system.json" 
  map='user.map' automap=create
;
libname out '\\DW\Dataout';

data out.data_&dato_d._system;
  set IN.ROOT;
run;

Re: read daily json datafiles

ANLYNG — Fri, 16 Oct 2020 16:45:23 GMT

Thank you very much for your solution. It works as expected now.

Re: read daily json datafiles

Reeza — Fri, 16 Oct 2020 17:37:15 GMT

%let dato_d = %sysfunc( today() - 3, yymmddn8.);

Re: read daily json datafiles

Tom — Fri, 16 Oct 2020 17:48:21 GMT

@Reeza wrote:
%let dato_d = %sysfunc( today() - 3, yymmddn8.);

That is not going to work. The TODAY() function needs to be wrapped in %SYSFUNC().

You could use the second argument to %SYSFUNC() to reference a format, but you cannot also do the subtraction. So you could do:

%let dato_d = %sysfunc( today() , yymmddn8.);

But to subtract the 3 days you need to do more. So you could use PUTN() to format the result of subtracting 3.

And you can do that without an explicit call to %EVAL() when it is in the argument passed to the PUTN() function.

%let dato_d = %sysfunc(putn( %sysfunc(today()) - 3, yymmddn8.));