Distinguishing SAS dataset output from multiple JSON files

[chatur]

Wanted to see this forum had some advice on the following:

I need to perform multiple tests for a process. The testing involves submitting one JSON file for each test instance. The JSON file includes attributes related to the process that I am trying to validate. One test case would have one set of attribute values. Another test case would have a different set of attribute values. Otherwise the tests, and submitted JSON files are the same/similar.

The process would provide a response back for each test case in the form of a JSON file. The response JSON file is analyzed to validate the test results. I use SAS to convert the response JSON file to SAS datasets for analysis. When I convert the response JSON file to SAS datasets, it produces  20 SAS datasets.

So,

Request_1.JSON  -->  Response_1.JSON  -->  [20 SAS datasets]

Here is the problem I am trying to solve. Each JSON response file, irrespective of the response file number (i.e., Response_1, Response_2,…, Response_n), produces 20 SAS datasets which have the same set of 20 dataset names (i.e., DSN_1, DSN_2, DSN_3,…, DSN_20). At this time, I have to run SAS code individually to convert each response JSON file to SAS datasets. If I run all the response files together using a do loop, each iteration will overwrite the SAS datasets produced by the previous iteration. I want to be able to produce the 20 SAS datasets that distinguish between the different response files.

Hope this request makes sense. I am happy to provide additional clarification if needed.

Thanks to all who respond.

[ballardw]

You need to show how you are writing that "do loop" that outputs the data sets.

If you are overwriting existing output then it means you aren't building your names correctly. So you should show the actual names that you want to build and where that information might be stored and how it relates to that "response" file.

Personally I am not even going to attempt which of the possibly 100's of methods there are available to name/missname files you might have used.

Be prepared to provide example code at different places.

[chatur]

Thanks for your response.

Here is something I am trying:

There are three JSON files: JSON_response_Appl_1 through 3

I place them all in the folder "C:\Users\U033564\Desktop\SAS\JSON\Test_directory"

Using SAS code, I create three sub folders: ...\Test_Directory|Appl_1 through

Then I try to convert the three JSON files into the three corresponding sub folders.

Here is the sample code. However, it has some bugs and isn't working. Would still need to rename the datasets that are created in each of the folders to include the Appl_1 to 3 in their names, as well as create a variable (key) which would be the Appl_1 to 3 so that I can aggregate the information from the various datasets.

Thanks for your assistance.

options dlcreatedir;

%local i next_name;

%macro create_dir(name_list);

%let i=1;

%do %while (%scan(&name_list, &i) ne );

   %let next_name = %scan(&name_list, &i);

libname newdir_&i "C:\Users\Desktop\SAS\JSON\Test_Directory\&next_name";

libname JSON_DIR json "C:\Users\Desktop\SAS\JSON\Test_directory\JSON_response_&next_name.json";

proc copy inlib=JSON_DIR outlib=newdir_&i;

run;

proc datasets lib=newdir_&i; run; quit;

%put &i &next_name;

   %let i = %eval(&i + 1);

%end;

%mend create_dir;

%create_dir(Test_1 Test_2 Test_3);

[Tom · Accepted solution]

There is no need to increment the counter by hand.

The way to write an iterative DO loop (or %DO loop) is 

do i=1 to 10;
%do i=1 %to 10;

You can ask SAS to count the number of filenames in your list.

Make sure you tell %SCAN() and COUNTW() what character you are using to delimit the list of names.

%do i=1 %to %sysfunc(countw(&name_list,%str( )));
   %let next_name=%scan(&name_list,&i,%str( ));

Make sure to include the period before the json extension on your files.  Your current code only has the period that tells SAS where the name of the macro variable ends.

Also the %LOCAL statement needs to be INSIDE the macro definition.

%macro create_dir(name_list);
%local i next_name ;
%do i=1 %to %sysfunc(countw(&name_list,%str( )));
   %let next_name=%scan(&name_list,&i,%str( )); 

libname out "C:\Users\Desktop\SAS\JSON\Test_Directory\&next_name";
libname in json "C:\Users\Desktop\SAS\JSON\Test_directory\JSON_response_&next_name..json";
proc copy inlib=in outlib=out;
run;

%end;
%mend create_dir;

 
options dlcreatedir mprint;
%create_dir(Test_1 Test_2 Test_3);

Also make sure that SAS is actually running on the machine where the C:\Users\Desktop\SAS\JSON\Test_Directory directory exists.  If you are using some front end like SAS/Studio or Enterprise Guide to submit your SAS code there is a very good chance that SAS cannot see your C: drive.

[chatur]

I appreciate you taking the time to look at my code and provide feedback and corrections.

Let me give it a try and I will get back with how it worked.

Best.

[chatur]

Thanks again. The code worked very well.

[chatur]

One addendum to the code I am trying to write. It adds one more step by creating a sub folder within the each of the newly created folders (say, parent folders) where all the JSON files have been extracted to SAS data sets, and then renames all the SAS data sets into this new sub folder.

The reason I need to rename the SAS data sets (to a shorter name) is that the length of many of these data set names is longer than 32 characters, and SAS errors out when working with those data sets.

I have included the code for reference.

It works fine with the first folder, where it creates that folder, extracts the JSON file into multiple data sets, creates a sub folder and renames those data sets. However, for all the subsequent parent folders where other JSON files have been extracted, it dos not carry out the next steps of creating a subfolder and renaming the data sets.

Wondering what I am doing wrong.

Also, is there a better way to address the name length >32 issue?

Thank you again!

%macro change_dsn(dsn_list,new_dsn_list);
%local i next_name ;
%do i=1 %to %sysfunc(countw(&dsn_list,%str( )));

%let next_name=%scan(&dsn_list,&i,%str( ));
%let new_name = %scan(&new_dsn_list,&i,%str( ));

libname dsn_in "C:\Zoot_response_Appl_1";
libname dsn_out "C:\Zoot_response_Appl_1\New_name_Appl_1";

%put &next_name &new_name;

data dsn_out.&new_name;
APP_ID = "Appl_1";
set dsn_in.&next_name;
run;

%end;
%mend change_dsn;

options dlcreatedir mprint;

%macro create_dir(name_list);
%local i next_name ;
%do i=1 %to %sysfunc(countw(&name_list,%str( )));
%let next_name=%scan(&name_list,&i,%str( ));

libname out "C:\Zoot_response_&next_name";
libname in json "C:\Zoot_response_&next_name..json";
proc copy inlib=in outlib=out;
run;

%change_dsn(DS1 DS2 DS3, A1 A2 A3)

%end;
%mend create_dir;

%create_dir(Appl_1 Appl_2 Appl_3);

[Tom]

You are not showing any dataset names that are longer than 32 bytes.  Names of JSON files are NOT dataset names. Subdirectory names are not dataset names.

When you try to read the contents of the JSON file using the JSON libref engine (the PROC COPY step) are you getting any error messages about names that are too long?  If so the solution is to create your own map file to tell the JSON engine how to convert the JSON text into SAS datasets.

[chatur]

The SAS data set names are pretty long (although I am not showing them to comply with my company's privacy policy). One the smallest ones is alldata. 

I do not get an error message with the COPY starement. Hoowever, when I try to rename some of the data sets,  SAS skips over those that are longer than 32 characters.