topic Re: Infile varchar issue in SAS Programming

Infile varchar issue

acordes — Wed, 15 Jan 2025 16:18:17 GMT

My task is to retrieve the json content information from all reports in a specific folder.

That works quite good but my content variable is limited to 32167 although I try to make it varchar.

When I lookup the column properties of the content variable in the casuser.info table it confirms Type: variable character with a length of 800000 (why? I put 200000 in the length statement).

But the main issue is that only 32167 characters at maximum carry over to this variable.

%sysmacdelete readReportContent;
%macro readReportContent (url, name, path, endpoint, output);
filename content clear;
filename content temp;
 
proc http
url="&url.%trim(&endpoint)/content"
out=content
oauth_bearer=sas_services;
headers
"Accept"="application/vnd.sas.report.content+json";
quit;
 
data casuser.info;
length report $128
path $1024
content varchar(200000);
infile content;
input;
report="&name";
path="&path";
content=substrn(_infile_, 1, 200000);
run;
 
data _null_;
set casuser.info;
where path ne '';
location=tranwrd(path, "/", "_");
mcall=cat('%writeJson(', trim(report), ',', trim(location), ')');
call execute(mcall);
run;
 
/* proc sql; */
/* drop table info; */
/* quit; */
%mend;

Re: Infile varchar issue

Tom — Wed, 15 Jan 2025 16:31:14 GMT

I do not see any code in there that is trying to read the JSON file.

Looks like CAS allows such extreme variables, but if your actual code is using SAS 9.4 code then variables will be limited to 32K bytes.

PS I suspect that CAS allocated 800K bytes for the variable because it assumed you meant 200K CHARACTERS and there are some UTF-8 characters that require 4 bytes.

Re: Infile varchar issue

acordes — Tue, 21 Jan 2025 12:31:17 GMT

I attach the full code.

As I had mentioned before, it runs without error and creates a file for each report in the target folder. The text is a json format that allows you to rebuild the Visual Analytics report by loading the pasted json string into a blank report.

BUT it cuts at 32167 bytes which is far to short to describe more sophisticated reports.

cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");
caslib _all_ assign;

/* --- Begin Edit --- */
%let BASE_URI=%sysfunc(getoption(servicesbaseurl));

%put &base_uri.;

%let baseurl=https://XXXXXXXXXX;
 
filename folders temp;
proc http
url = "https://XXXXXXXXXX"
out= folders
oauth_bearer = sas_services;
headers
'Accept'= 'application/vnd.sas.collection+json';
run;
libname folders clear;
libname folders json;
 
%let hostname = https://XXXXXXXX;
%let endpoint = /folders/folders;
%let path = "/Public/ODAP_REPORTS" ;
%let output = "/in_pdf";
 
cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");
caslib _all_ assign;

/**********************************************************/
/* Retrieve the ID of the folder where the report resides */
/**********************************************************/
filename folders clear;
filename folders temp;
 
proc http
url = "&hostname.&endpoint/@item"
query = ("path"=&path)
out= folders
oauth_bearer = sas_services;
headers
'Accept'= 'application/vnd.sas.content.folder+json';
run;
 
libname folders clear;
libname folders json;
 
/*************************************************/
/* Get a list of objects in that specific folder */
/*************************************************/
/* Identify the endpoint to be used */
proc sql ;
select href, type into :endpoint, :type
from folders.links
where method="GET" and rel = "members";
quit;
 
/* Retrieve the list of objects */
filename memList clear;
filename memList temp;
 
proc http
url = "&hostname.%trim(&endpoint)"
out= memList
oauth_bearer = sas_services;
headers
"Accept"= "%trim(&type)+json";
run;
 
libname memList clear;
libname memList json;
 
/******************************************************/
/* Extract report content (structure) for each report */
/******************************************************/
/* Macro to write the output to a json file */
%sysmacdelete writeJson;
%macro writeJson(name, path);
 
filename outcsv FILESRVC FOLDERPATH='/Users/XXXX@company.com/My Folder/in_pdf'  FILENAME="_&name..json";
proc json out=outcsv nosastags pretty noscan   ;
export casuser.info;
run;
 
%mend;
 
/* Macro to read the report content and generate the output file */
%sysmacdelete readReportContent;
%macro readReportContent (url, name, path, endpoint, output);
filename content clear;
filename content temp;
 
proc http
url="&url.%trim(&endpoint)/content"
out=content
oauth_bearer=sas_services;
headers
"Accept"="application/vnd.sas.report.content+json";
quit;
 
data casuser.info;
length report $128
path $1024
content varchar(200000);
infile content;
input;
report="&name";
path="&path";
content=substrn(_infile_, 1, 200000);
run;
 
data _null_;
set casuser.info;
where path ne '';
location=tranwrd(path, "/", "_");
mcall=cat('%writeJson(', trim(report), ',', trim(location), ')');
call execute(mcall);
run;
 
/* proc sql; */
/* drop table info; */
/* quit; */
%mend;
 

/* Generate a view containing information to call readReportContent */
proc sql;
create table merged as
select "&hostname" as url,
a.ordinal_items,
a. name,
a.uri,
b.href,
b.type ,
b.method
from memlist.items as a
left join memlist.items_links as b
on a.ordinal_items=b.ordinal_items
having a.contentType="report" and b.rel="getResource";
quit;
 
options mlogic;
 
/* Call readReportContent macro for each report */
data _null_;
length mcall $1024;
set merged(firstobs=1);
out=&output;
path=&path;
mcall=cat('%readReportContent(', trim(url), ",", trim(name), ",", trim(path),",", trim(href), ",", trim(out), ')');
call execute(mcall);
run;

Re: Infile varchar issue

yabwon — Tue, 21 Jan 2025 14:24:11 GMT

For varchar 200000 is number of characters symbols, 800000 is number of bytes.

To read in data try:

filename f "R:\text.txt" lrecl=200000;

data _null_;
file f;
do x = "B", "C", "D";
do i = 1 to 200000-99;
put "A" @;
end;
put x;
end;
run;


%let n=12;

data _null_1;
length c1-c&n. varchar(32767) d varchar(200000);
infile f lrecl=200000 truncover;
input (c1-c&n.) ($32767.);

d=cats(of c1-c&n.);

array c[*] c1-c&n. d;
do i=1 to dim(c);
y=length(c[i]);
z=lengthm(c[i]); /*in memory length*/
w=lengthn(c[i]); /*real length, 0 for empty string */
n=vname(c[i]);
put n= @10 y @20 z @30 w ;
end;

drop c1-c&n. d;
put "---------------------------";
run;

But remember if you want to save it as SAS7BDAT file it will truncate all char variables to 32767,

Since you seems to be working on Viya, you could try to save it as SASHDAT (memory drop) format.

Bart

Re: Infile varchar issue

Tom — Tue, 21 Jan 2025 14:51:18 GMT

I cannot figure out what your issue is.

Did that code not run? If so what errors did you get?

Did it not produce the right result? How do you know?

Can't you at least narrow it down to where in the multistep process the issue is?

I see for example you are making a libref using the JSON engine. Is the issue that the JSON engine cannot read long strings?

Re: Infile varchar issue

acordes — Tue, 21 Jan 2025 14:53:30 GMT

Hi @Tom

My code as posted here runs 'successfully' in my environment.

But at some point I lose that part of the string that surpasses 32167 bytes.

There's no error in the log.

Re: Infile varchar issue

Tom — Tue, 21 Jan 2025 16:43:36 GMT

@acordes wrote:

I attach the full code.

As I had mentioned before, it runs without error and creates a file for each report in the target folder. The text is a json format that allows you to rebuild the Visual Analytics report by loading the pasted json string into a blank report.

BUT it cuts at 32167 bytes which is far to short to describe more sophisticated reports.

Which part is the "pasted json string"?

Which part is the "blank report"?

Which step is it that is trying to load the "pasted json string" into a "blank report"?

You should try to figure out if the issue is with your attempt to modify the blank report. Or if the issue is that you did modify the JSON properly but whatever system that is supposed to LOAD it does not accept such long strings.

Remember that JSON files are just TEXT. So you can look at them with any text editor. Or even with a simple SAS data step like:

data _null_;
  infile "myjsonfilenamehere" recfm=f lrecl=100;
  input;
  list;
run;

Re: Infile varchar issue

acordes — Tue, 21 Jan 2025 16:45:46 GMT

Hi @yabwon Bart

This looks very promising.

The text.txt is created correctly.

But when I apply it for my program I get the following error message:

NOTE: Use of overflow option (MISSOVER, TRUNCOVER, STOPOVER) with binary file (RECFM=N) is dependent on the record length. This
can produce unexpected results.
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The infile F is:
Filename=text.txt,
URI path=/files/files/12ac4e1a-28f6-45b8-ac14-04bd84c881d0,
File Identifier=12ac4e1a-28f6-45b8-ac14-04bd84c881d0,
Content Type=text/plain,Encoding=UTF-8,
Searchable=false,RECFM=V,LRECL=200000,
File Size (bytes)=20623,
Last Modified=21Jan2025:16:27:10,
Create Time=21Jan2025:15:29:53
NOTE: Unexpected end of file for binary input.
NOTE: The data set CASUSER.INFO has 0 observations and 3 variables.


cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");
caslib _all_ assign;

/* --- Begin Edit --- */
%let BASE_URI=%sysfunc(getoption(servicesbaseurl));

%put &base_uri.;

%let baseurl=https://xxxxxxxxxx;
 
filename folders temp;
proc http
url = "https://xxxxxxxx/folders/folders"
out= folders
oauth_bearer = sas_services;
headers
'Accept'= 'application/vnd.sas.collection+json';
run;
libname folders clear;
libname folders json;
 
%let hostname = https://xxxxxxxxx;
%let endpoint = /folders/folders;
%let path = "/Public/ODAP_REPORTS" ;
%let output = "/in_pdf";
 
/**********************************************************/
/* Retrieve the ID of the folder where the report resides */
/**********************************************************/
filename folders clear;
filename folders temp;
 
proc http
url = "&hostname.&endpoint/@item"
query = ("path"=&path)
out= folders
oauth_bearer = sas_services;
headers
'Accept'= 'application/vnd.sas.content.folder+json';
run;
 
libname folders clear;
libname folders json;
 
/*************************************************/
/* Get a list of objects in that specific folder */
/*************************************************/
/* Identify the endpoint to be used */
proc sql ;
select href, type into :endpoint, :type
from folders.links
where method="GET" and rel = "members";
quit;
 
/* Retrieve the list of objects */
filename memList clear;
filename memList temp;
 
proc http
url = "&hostname.%trim(&endpoint)"
out= memList
oauth_bearer = sas_services;
headers
"Accept"= "%trim(&type)+json";
run;
 
libname memList clear;
libname memList json;
 
/******************************************************/
/* Extract report content (structure) for each report */
/******************************************************/
/* Macro to write the output to a json file */
%sysmacdelete writeJson;
%macro writeJson(name, path);
 
filename outcsv FILESRVC FOLDERPATH='/Users/xxxxxxx@comp.com/My Folder/in_pdf'  FILENAME="_&name..json";
proc json out=outcsv nosastags pretty noscan   ;
export casuser.info;
run;
 
%mend;
 
/* Macro to read the report content and generate the output file */
%sysmacdelete readReportContent;
%macro readReportContent (url, name, path, endpoint, output);
filename f clear;
/* filename f temp; */
filename f FILESRVC FOLDERPATH='/Users/xxxxxxx@comp/My Folder/in_pdf/'  FILENAME="text.txt" lrecl=200000;
 
proc http
url="&url.%trim(&endpoint)/content"
out=f
oauth_bearer=sas_services;
headers
"Accept"="application/vnd.sas.report.content+json";
quit;

%let n=12;

data casuser.info;
length report $128 path $1024 c1-c&n. varchar(32767) content varchar(200000);
infile f lrecl=200000 truncover;
input (c1-c&n.) ($32767.);

content=cats(of c1-c&n.);
array c[*] c1-c&n. content;
report="&name";
path="&path";
drop c1-c&n.;
run;
 
/* data casuser.info; */
/* length report $128 */
/* path $1024 */
/* content varchar(200000); */
/* infile content; */
/* input; */
/* report="&name"; */
/* path="&path"; */
/* content=substrn(_infile_, 1, 200000); */
/* run; */
 
data _null_;
set casuser.info;
where path ne '';
location=tranwrd(path, "/", "_");
mcall=cat('%writeJson(', trim(report), ',', trim(location), ')');
call execute(mcall);
run;
 
/* proc sql; */
/* drop table info; */
/* quit; */
%mend;
 

/* Generate a view containing information to call readReportContent */
proc sql;
create table merged as
select "&hostname" as url,
a.ordinal_items,
a. name,
a.uri,
b.href,
b.type ,
b.method
from memlist.items as a
left join memlist.items_links as b
on a.ordinal_items=b.ordinal_items
having a.contentType="report" and b.rel="getResource";
quit;
 
options mlogic;
 
/* Call readReportContent macro for each report */
data _null_;
length mcall $1024;
set merged(firstobs=1 obs=1);
out=&output;
path=&path;
mcall=cat('%readReportContent(', trim(url), ",", trim(name), ",", trim(path),",", trim(href), ",", trim(out), ')');
call execute(mcall);
run;

Re: Infile varchar issue

yabwon — Tue, 21 Jan 2025 16:54:17 GMT

looks like RECFM= for the file:

filename f FILESRVC FOLDERPATH='/Users/xxxxxxx@comp/My Folder/in_pdf/' FILENAME="text.txt" lrecl=200000;

is set to N (binary)

Try:

filename f FILESRVC FOLDERPATH='/Users/xxxxxxx@comp/My Folder/in_pdf/' FILENAME="text.txt" lrecl=200000 RECFM=V;