topic iterate through the records of a sas table in SAS Programming

iterate through the records of a sas table

sateh — Thu, 10 Nov 2022 02:01:16 GMT

I have a table in sas (see image) where I need to loop through each record to generate a json for each record (product).

each json generated must have the name of the product.

my code to generate my json is this:

I really don't know how I should do this. 😞

Re: iterate through the records of a sas table

Tom — Thu, 10 Nov 2022 03:58:51 GMT

I am not sure what you mean by "a json".

If you want to generate a JSON file that has the information the dataset you took the selfie of then the code you posted the second selfie of looks reasonable.

How is the resulting file different than what you wanted?

Note: If you want coding help post the data as DATA and not as PICTURES. Also explain the output you want more clearly. Proving the desired output for the example input data you share will go long way to help clarify your description of the desired results.

Re: iterate through the records of a sas table

sateh — Thu, 10 Nov 2022 13:33:27 GMT

Yes, exactly what I need is a JSON file for each product, There should be 8 JSON files left because there are 8 products (records) in the table.
the result should be for example:
product_8085.json
product_8076.json
product_8087.json
product_8025.json
product_8060.json
product_8745.json
product_9528.json
product_6058.json
attached image of my data

Re: iterate through the records of a sas table

andreas_lds — Thu, 10 Nov 2022 13:41:35 GMT

and what will be the content of the json files?

Re: iterate through the records of a sas table

Tom — Thu, 10 Nov 2022 13:59:27 GMT

If you want to generate a lot of text files from a dataset (a JSON file is just a text file) then use a data step. You can use the FILEVAR= option of the FILE statement to name a variable that will contain the name of the file to be written.

So the general structure of your program could be:

data _null_;
  set myfile;
  length filename $200 ;
  filename = cats('product_',product,'.json');
  file json filevar=filename;
*   code here to write the json text using PUT statement ;
run;

If your dataset only has the 3 or four variables you show in your photograph then the PUT statement to write JSON text will be trivial. But you need to show an example of the JSON format you actually want to make. And explain what parts of it need to come from the values of the variables in the dataset. If the MYFILE dataset has multiple observations for the same product then you will probably need to make sure it is sorted and then you can use BY group processing in the dataset.

Re: iterate through the records of a sas table

sateh — Thu, 10 Nov 2022 14:15:13 GMT

This is an example of how a JSON file of a single product should look like, in this case the first product 8085

Re: iterate through the records of a sas table

Reeza — Thu, 10 Nov 2022 15:12:27 GMT

Post your data and code as text, retyping it is a pain in the ...;

Basically you can turn your small proc json into a macro and call it once for each line using CALL EXECUTE.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

%macro create_json(producto=);
proc json out="/home/folder_json/producto_&producto..json" pretty nosastags;
export public.productos(where=id_producto=&producto).);
quit;

%mend;

*call macro for each line;
data _null_;
set public.productos;
str = catt('%create_json(producto=', id_producto, ');');
call execute(str);
run;

@sateh wrote:

I have a table in sas (see image) where I need to loop through each record to generate a json for each record (product).

each json generated must have the name of the product.

my code to generate my json is this:

I really don't know how I should do this. 😞

Re: iterate through the records of a sas table

sateh — Thu, 10 Nov 2022 16:14:34 GMT

Thank you, this approach really helped me a lot.