cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed

SID: Script to Update a Lookup Table

Started ‎03-01-2022 by
Modified ‎03-01-2022 by
Views 5,636

In my last article, I equated SID global variables to SID lookup tables and showed how to update a global variable via REST. Well, it turns out the REST API for updating SID lookup tables is a bit different than the API for global variables. Let's look at the differences here and create a script to update SID lookup tables.

 

In order to update our lookup table, we'll have to perform the following steps. Some of these were covered in the previous post. Some are different. Some are new.

 

  1. Find the lookup table URI
  2. Post a new version to the lookup table.
  3. Add lookup table entries to the new version.
  4. Activate the new version.

 

But First, a Word on Authentication

Like Xavier Bizoux  did here, we'll use the oauth_bearer = sas_services PROC HTTP option within SAS Studio to piggy back on the authentication already established by the user interface. Of course, if you run the script outside of SAS Studio, you'll need to authenticate using some other mechanism. To explore Viya authentication options, see Joe Furbee's post here. .

 

1. Find the Lookup Table URI

In the previous post, we looked up the URI using the SID user interface. Here we do it with code. First we use PROC HTTP to retrieve all of the visible lookup tables ("reference data domains") and then a DATA Step filters the list to find the one we want, name = "test". Finally the URI is placed in a macro variable, lkpURI.

 

filename lookups temp;

proc http
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/"
    out= lookups
    oauth_bearer = sas_services;
    headers
         "Accept"="application/vnd.sas.collection+json";
run;

libname lookups json;

data _null_;
set lookups.items;
where name="test";
call symput('lkpURI',id);
run;

%put &lkpURI;

 

For information on the API call, see the doc here.

 

For information on using PROC HTTP to call the Viya API, see Xavier Bizoux's article here.

 

2. Post a New Version to the Lookup Table

Unlike global variables, we can't update the (current version of the) lookup table directly. We can only update lookup table versions. So, instead of trying to find the specific version we want update, we'll just create a new one.

 

Below we POST a simple message to our lookup table URI contents to create a new version labelled "Yet Another One." We set the status to "developing" so that we can add entries to it later.

 

Once our new version is created, we put its URI into the macro variable, vrsnURI.

 

proc http
    method = "POST"
    url = https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents
    in = '{"label": "Yet Another One","status":"developing"}'
    out= response
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8";
run;

libname response json;

data _null_;
set response.root;
call symput('vrsnURI',id);
run;

%put &vrsnURI;

 

3. Add Lookup Table Entries

Now we'll add lookup table entries to our new lookup table version. These will, of course, be different to active version's lookup table entries. For example, say our lookup table has the current entries,

 

1 Eeny
2 Meeny
3 Miney
4 Moe

 

...and we want to change them to:

 

1 Oneay
2 Wotay
3 Eathray
4 Ourfay

 

...the API request would look like the below code. Again, we post the desired entries to the lookup table's version entries endpoint,

/referenceData/domains/&lkpURI/contents/&vrsnURI/entries.

 

filename entryRes temp;

proc http
    method = "POST"
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI/entries"
    in = '[{"key": "1","value": "Oneay"},
          {"key": "2","value": "Wotay"},
          {"key": "3","value": "Eathray"},
          {"key": "4","value": "OurFay"}]'
    out= entryRes
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8";
run;

libname entryRes json;

 

4. Activate the New Version

Unlike SID global variables, there is no activate action for lookup tables. Instead we update the status of lookup table version we want to activate.

.

4.1 GET the Lookup Table Version ETag

Any time we want to update an object, we need to get it's ETag. We need the ETag when we perform the update in the next step. REST uses the ETag to help avoid update collisions.

 

The Etag is output in the response header which unfortunately doesn't come out as nicely formatted json. So we do a bit of DATA Step manipulation to grab it. I've output the entire response header to the log, posted below, for your inspection.

 

filename lkpvrsn temp;
filename lkpvhdr temp;

proc http 
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI"
    out=lkpvrsn
    headerout=lkpvhdr
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8";
run;

libname lkpvrsn json;

data _null_;
infile lkpvhdr;
input;
put _infile_;
if length(_infile_) > 4 then if substr(_infile_,1,4)="ETag" then do;
   call symput('etag',scan(_infile_,2));
end;
run;

%put &etag;

 

4.2 Update the Lookup Table Version Status

With both the lookup table version URI and its ETag, we can now update the lookup table version. The HTTP request is below.

 

filename prodVrsn temp;

proc http
    method = "PUT"
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI"
    in = '{"status":"production"}'
    out= prodVrsn
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8"
         "If-Match"=%tslit(&etag);
run;

libname prodVrsn json;

 

Verifying the Update

Looking in Intelligent Decisioning, we can verify that the "test" lookup table is updated with the new entries and the version with the new entries is active. Although the version label isn't available in the UI, we can verify the version by matching the version URI with the SAS log, posted below.

 

sf_1_lookupTableAfter-1024x760.png

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

 

.

Full Code and Log

Below you'll find the full script and execution log. As mentioned before, you'll need to customize the script to add the authentication that makes sense for your situation. You can of course, leave the authentication as is if you plan on running it in SAS Studio. You'll also need to change the initial query conditions (where name="test" in the example) to find your particular lookup table as well as the lookup table entries. .

 

Code:

** GET Lookup Table URI;

filename lookups temp;

proc http
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/"
    out= lookups
    oauth_bearer = sas_services;
    headers
         "Accept"="application/vnd.sas.collection+json";
run;

libname lookups json;

data _null_;
set lookups.items;
where name="test";
call symput('lkpURI',id);
run;

%put &lkpURI;

** Post a new version to the lookup table;

filename response temp;

proc http
    method = "POST"
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents"
    in = '{"label": "One More","status":"developing"}'
    out= response
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8";
run;

libname response json;

data _null_;
set response.root;
call symput('vrsnURI',id);
run;

%put &vrsnURI;

** Add Entries;

filename entryRes temp;

proc http
    method = "POST"
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI/entries"
    in = '[{"key": "1","value": "Oneay"},
          {"key": "2","value": "Wotay"},
          {"key": "3","value": "Eathray"},
          {"key": "4","value": "OurFay"}]'
    out= entryRes
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8";
run;

libname entryRes json;

** Get the Lookup Table Version Etag;

filename lkpvrsn temp;
filename lkpvhdr temp;

proc http 
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI"
    out=lkpvrsn
    headerout=lkpvhdr
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8";
run;

libname lkpvrsn json;

data _null_;
infile lkpvhdr;
input;
put _infile_;
if length(_infile_) > 4 then if substr(_infile_,1,4)="ETag" then do;
   call symput('etag',scan(_infile_,2));
end;
run;

%put &etag;

** Activate the new version (Set Status to "Production");

filename prodVrsn temp;

proc http
    method = "PUT"
    url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI"
    in = '{"status":"production"}'
    out= prodVrsn
    oauth_bearer = sas_services;
    headers
         "Accept"="application/json"
         "Content-Type"="application/json;charset=utf-8"
         "If-Match"=%tslit(&etag);
run;

libname prodVrsn json;

.

Log:

 

1    %studio_hide_wrapper;
77   
78   ** GET Lookup Table URI;
79   
80   filename lookups temp;
81   
82   proc http
83       url = "https://live.lts.gel.race.sas.com/referenceData/domains/"
84       out= lookups
85       oauth_bearer = sas_services;
86       headers
87            "Accept"="application/vnd.sas.collection+json";
88   run;
NOTE: 200 OK
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.40 seconds
      cpu time            0.07 seconds
      
89   
90   libname lookups json;
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref LOOKUPS was successfully assigned as follows: 
      Engine:        JSON 
      Physical Name: 
      /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b
      bee-4180-b29f-f3a4253faf46-qrm4r/#LN00082
91   
92   data _null_;
93   set lookups.items;
94   where name="test";
95   call symput('lkpURI',id);
96   run;
NOTE: There were 1 observations read from the data set LOOKUPS.ITEMS.
      WHERE name='test';
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
97   
98   %put &lkpURI;
47944623-dd84-41de-a7d2-5a4ab507e0c9
99   
100  ** Post a new version to the lookup table;
101  
102  filename response temp;
103  
104  proc http
105      method = "POST"
106      url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents"
107      in = '{"label": "One More","status":"developing"}'
108      out= response
109      oauth_bearer = sas_services;
110      headers
111           "Accept"="application/json"
112           "Content-Type"="application/json;charset=utf-8";
113  run;
NOTE: 201 Created
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.20 seconds
      cpu time            0.05 seconds
      
114  
115  libname response json;
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref RESPONSE was successfully assigned as follows: 
      Engine:        JSON 
      Physical Name: 
      /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b
      bee-4180-b29f-f3a4253faf46-qrm4r/#LN00086
116  
117  data _null_;
118  set response.root;
119  call symput('vrsnURI',id);
120  run;
NOTE: There were 1 observations read from the data set RESPONSE.ROOT.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
121  
122  %put &vrsnURI;
84e0d6cd-2ca2-4a19-aee6-c6596c4ec3f7
123  
124  ** Add Entries;
125  
126  filename entryRes temp;
127  
128  proc http
129      method = "POST"
130      url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI/entries"
131      in = '[{"key": "1","value": "Oneay"},
132            {"key": "2","value": "Wotay"},
133            {"key": "3","value": "Eathray"},
134            {"key": "4","value": "OurFay"}]'
135      out= entryRes
136      oauth_bearer = sas_services;
137      headers
138           "Accept"="application/json"
139           "Content-Type"="application/json;charset=utf-8";
140  run;
NOTE: 201 Created
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.18 seconds
      cpu time            0.02 seconds
      
141  
142  libname entryRes json;
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref ENTRYRES was successfully assigned as follows: 
      Engine:        JSON 
      Physical Name: 
      /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b
      bee-4180-b29f-f3a4253faf46-qrm4r/#LN00089
143  
144  ** Get the Lookup Table Version Etag;
145  
146  filename lkpvrsn temp;
147  filename lkpvhdr temp;
148  
149  proc http
150      url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI"
151      out=lkpvrsn
152      headerout=lkpvhdr
153      oauth_bearer = sas_services;
154      headers
155           "Accept"="application/json"
156           "Content-Type"="application/json;charset=utf-8";
157  run;
NOTE: 200 OK
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.15 seconds
      cpu time            0.04 seconds
      
158  
159  libname lkpvrsn json;
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref LKPVRSN was successfully assigned as follows: 
      Engine:        JSON 
      Physical Name: 
      /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b
      bee-4180-b29f-f3a4253faf46-qrm4r/#LN00092
160  
161  data _null_;
162  infile lkpvhdr;
163  input;
164  put _infile_;
165  if length(_infile_) > 4 then if substr(_infile_,1,4)="ETag" then do;
166     call symput('etag',scan(_infile_,2));
167  end;
168  run;
NOTE: The infile LKPVHDR is:
      
      Filename=/opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-1
      4b63597-bbee-4180-b29f-f3a4253faf46-qrm4r/#LN00093,
      Owner Name=UNKNOWN,Group Name=UNKNOWN,
      Access Permission=-rw-r--r--,
      Last Modified=19Nov2021:15:32:24,
      File Size (bytes)=708
HTTP/1.1 200 
Date: Fri, 19 Nov 2021 20:32:24 GMT
Server: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips
Content-Type: application/vnd.sas.data.reference.domain.content+json
Content-Security-Policy: default-src 'self'; script-src 'self' 'unsafe-inline' 'unsafe-eval'; img-src 'self' *.sas.com blob: data:; 
style-src 'self' 'unsafe-inline'; child-src 'self' blob: data: mailto:;
ETag: "kw6u9hfg"
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
Expires: 0
Strict-Transport-Security: max-age=15724800; includeSubDomains
X-Frame-Options: SAMEORIGIN
Keep-Alive: timeout=5, max=100
Connection: Keep-Alive
NOTE: 16 records were read from the infile LKPVHDR.
      The minimum record length was 0.
      The maximum record length was 203.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
169  
170  %put &etag;
"kw6u9hfg"
171  
172  ** Activate the new version (Set Status to "Production");
173  
174  filename prodVrsn temp;
175  
176  proc http
177      method = "PUT"
178      url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI"
179      in = '{"status":"production"}'
180      out= prodVrsn
181      oauth_bearer = sas_services;
182      headers
183           "Accept"="application/json"
184           "Content-Type"="application/json;charset=utf-8"
185           "If-Match"=%tslit(&etag);
186  run;
NOTE: 200 OK
NOTE: PROCEDURE HTTP used (Total process time):
      real time           4.72 seconds
      cpu time            0.04 seconds
      
187  
188  libname prodVrsn json;
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref PRODVRSN was successfully assigned as follows: 
      Engine:        JSON 
      Physical Name: 
      /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b
      bee-4180-b29f-f3a4253faf46-qrm4r/#LN00096
189  
190  
191  
192  %studio_hide_wrapper;
200

 

Next Steps

You can, of course, develop this process further by parameterizing the code and making the lookup table entries data driven. I didn't tackle anything like that here as I wanted the code as simple as possible for ease of understanding.

 

Find more articles from SAS Global Enablement and Learning here.

0 Likes
Version history
Last update:
‎03-01-2022 01:39 PM
Updated by:
SAS Employee
Contributors

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags