The SAS Viya platform supports access to the Databricks database using the SAS/ACCESS Interface to Spark components. The SAS/ACCESS interface to Spark includes two main components, including the Spark data connector. The Spark data connector enables the user to connect Spark-compatible data sources from CAS. The SAS Viya platform includes the SAS/ACCESS interface to Spark with the Simba Databricks JDBC driver to connect to the Databricks database. ... View more

Hello I have a macro variable called allv_y that contain many Var(field) names. I want to add single quote for each the fields names (each component) in macro var allv_y. The required macro variable is called b_allv_y The problem- When I create the new macro var I dont see all components!! Some of them disappeared   %let allv_Y=LAKOACH AB_PA_IND_bad_ANYBAD AB_PA_MKR_TASHLUM AB_PA_AGE_VETEK_MASHK AB_PA_Y_TAS_SUMLLL_TYPE AB_PA_BITCHONOT AB_NEWC_Y_NIZHOS AB_NEWC_Y_NIZMISGOSH_HARIGA AB_NEWC_AKM_HZR AB_NEWC_MASHKANTA AB_NEWC_INC_COMPONENT AB_NEWC_INC_TREND AB_NEWC_CDO_CCSCR AB_NEWC_Y_NIZMISGCRD AB_NEWC_OSHER_HOS AB_NEWC_IND_bad_ANYBAD AB_NEWC_Y_HOVA_ZHUT AB_NEWC_LLL_LEUMI_HUZB AB_NEWC_MESHIHOT_MEZMEZ AB_NEWC_AGE_BALUT AB_OFI1MB_Y_NIZMISGOSH_HARIGA AB_OFI1MB_SMLHOV_PIG_AKM AB_OFI1MB_CDO_CCSCR AB_OFI1MB_Y_ECHZERLOANINC AB_OFI1MB_MESHIHOT_MEZMEZ AB_OFI1MB_IND_bad_ANYBAD AB_OFI1MB_Y_NIZHOS_INC AB_OFI1MB_Y_HOVA_ZHUT AB_OFI1MB_VETEK_AGE_BALUT AB_OFI1MB_MESHIHOT_CHCK AB_OFI1MB_HUZB AB_OFI1MB_LOAN_BIT AB_YNG_Y_NIZMISGOSH_HARIGA AB_YNG_AKM_HZR AB_YNG_INC_COMPONENT AB_YNG_CDO_CCSCR AB_YNG_Y_NIZMISGCRD AB_YNG_Y_HOVA_ZHUT AB_YNG_IND_bad_ANYBAD AB_YNG_MESHIHOT_MEZMEZ AB_YNG_HUZB AB_YNG_SMLHOV AB_YNG_VETEK_AGE_BALUT AB_YNG_Y_NIZHOSBIT AB_ACTV_AGE_BALUT AB_ACTV_VETEK AB_ACTV_Y_NIZMISGCRD AB_ACTV_CDO_CCSCR AB_ACTV_MASHKANTA AB_ACTV_MESHIHOT_MEZMEZCHCK AB_ACTV_HUZB AB_ACTV_SMLHOV AB_ACTV_AKM_HZR AB_ACTV_IND_bad_ANYBAD AB_ACTV_OSHER AB_ACTV_Y_NIZMISGOSH_HARIGA AB_ACTV_Y_NIZHOSBIT AB_ACTV_Y_ECHZERLOANINC_RINC AB_ACTV_Y_HOVA_ZHUT_INCCOMP VALID_FROM_DTTM POP_LOMEFORAT_WPPR POP_LOMEFORAT_NOPPR VCT_AB_SIBAT_OR TEUR_BAD_TYPE_AB TEUR_REJ_TYPE_AB WWW_6_AMT AVG_WAGE_3_MONTH_AMT AGE_VALUE AKM_ABSH_1_CNT AKM_ABSH_2_CNT AKM_ABSH_3_CNT AKM_ABSH_4_CNT AKM_ABSH_5_CNT ANOEXTAS_HOD_LLL_AMT BITCHONOT_KVUIM_6_AMT BITCHONOT_NEZILIM_6_AMT D5MACHZOR_MTI_AMT HARIGA_MISGERET_1_AMT HARIGA_MISGERET_2_AMT HARIGA_MISGERET_3_AMT HARIGA_MISGERET_4_AMT HARIGA_MISGERET_5_AMT HZR_HIUV_1_CNT HZR_HIUV_2_CNT HZR_HIUV_3_CNT HZR_HIUV_4_CNT HZR_HIUV_5_CNT HZR_HIUV_6_CNT balance_MESHU_LLL_PIG_MTH_1_AMT balance_MESHU_LLL_PIG_MTH_2_AMT balance_MESHU_LLL_PIG_MTH_3_AMT balance_MESHU_LLL_PIG_MTH_4_AMT balance_MESHU_LLL_PIG_MTH_5_AMT balance_MESHU_LLL_PIG_MTI_1_AMT balance_MESHU_LLL_PIG_MTI_2_AMT balance_MESHU_LLL_PIG_MTI_3_AMT balance_MESHU_LLL_PIG_MTI_4_AMT balance_MESHU_LLL_PIG_MTI_5_AMT balance_MESHU_LLL_PIG_MTI_6_AMT balance_MESHU_LLL_PIG_MTH_6_AMT MACHZOR_NOEXT_AMT MAX_HARIGA_1_5_AMT MAX_PIGUR_1_5_AMT MAX_TAUN_check_NO_20_21_CD MISGERET_CARTIS_ASHRAI_6_AMT MISGERET_OSH_6_AMT MISPAR_D_HARIGA_1_CNT MISPAR_D_HARIGA_2_CNT MISPAR_D_HARIGA_3_CNT MISPAR_D_HARIGA_4_CNT MISPAR_D_HARIGA_5_CNT MISPAR_D_HARIGA_6_CNT NITZUL_6_AMT NITZUL_CARTIS_ASHRAI_6_AMT NIZUL_BAOSH_6_AMT OSHER_AMT WWW_1_AMT WWW_2_AMT WWW_3_AMT WWW_4_AMT WWW_5_AMT SEMEL_CHOV_1_CD SEMEL_CHOV_2_CD SEMEL_CHOV_3_CD SEMEL_CHOV_4_CD SEMEL_CHOV_5_CD SEMEL_CHOV_6_CD SUG_CARTIS_ASHRAI_6_CD TAUN_check_1_CD TAUN_check_2_CD TAUN_check_3_CD TAUN_check_4_CD TAUN_check_5_CD TAUN_check_6_CD TAUN_check_NO_20_21_6_CD VETEK_6_VALUE ANY_BAD AKM_ABSH_6_CNT D5WWW_AMT HASIMOT_6_CNT HSMVISA_KASPOMAT_1_6_CNT balance_MESHU_LLL_PIG_1_5_IND balance_MESHU_LLL_PIG_6_AMT balance_MESHU_LLL_PIG_6_IND MAX_TAUN_check_MODEL_1_6_CD MESHIHOT_SHKIM_1_AMT MESHIHOT_SHKIM_2_AMT MESHIHOT_SHKIM_3_AMT MESHIHOT_SHKIM_4_AMT MESHIHOT_SHKIM_5_AMT MESHIHOT_SHKIM_6_AMT MESHIHOT_SHKIM_6_CNT NIT_HOSEN_BIT_AMT SEMEL_BALUT_CD TAS_HOD_LLL_REGILA_6_AMT TAUN_check_MODEL_6_CD; data _null_; length b_allv_Y $2500; b_allv_Y = "'" || tranwrd("&allv_Y", " ", "' '") || "'"; call symputx('b_allv_Y', b_allv_Y); run;     ... View more

I am looking to translate coefficients obtained from a log-linear regression model into a "per unit incremental change" instead of the usual "percent change" interpretation so they can be used to make forecasts using inputs in a meaningful manner.   Concretely, I am seeking to estimate the average annual cost of adding an additional mile transporting student using budgetary data from school districts statewide as the outcome variable and the total student mileage (i.e., total number of transported students times the average distance from home to school) as the predictor:   Total_Trans_Cost = alpha + beta_1(Total_Trans_Miles)   In this linear regression model the coefficient for total student mileage (beta_1) would reflect the cost of adding one more transportation mile, and thus if I modelled last year's school budget data using last year's total mileage, I could make a prediction of this year's costs using this year's estimated total mileage using the coefficient as a multiplier.   My issue is, given the large disparity between total transportation costs in the millions of dollars verses total mileage in the thousands, I have to transform the data so as to yield a robust linear regression with normally distributed studentized residuals. After vetting a series of models, I found that taking the natural log of both the outcome and predictor yielded very satisfactory results from a statistical point of view, but now I do not know how to make a "per unit" cost estimate from the resulting coefficients. As is well known, a log-linear model yields the equivalent of elasticities in the coefficients, which in my case can readily be interpreted as "a one-percent increase in the total transportation miles is associated with a 1.01 * EXP(beta_1) percent change in total transportation costs." The problem is that I do not know how I can use this "one percent change" with new mileage estimates as they are new tallies and not changes to the prior year tally. My ultimate goal is to be able to just multiply by the "per mile cost" to get an estimate of how much it would cost to transport X number of students.   To spell it out more clearly, my log-linear model is ln(TOT_COST) = 6.928 + 0.886 ln(TOT_MI). The 0.886 converts to about a one percent change in total miles is associated with in 0.885 percent change total transportation cost. How do I make this into "X number of total miles results in Y change in total costs."   Thanks in advance for any advice or suggestions! Peter ... View more

In one of the many customer iterations I had recently, I was asked to help develop a solution to allow an image that was dynamically created by a third-party library/API to be embedded in a SAS Visual Analytics (VA) report. In addition to some input parameters passed to that external API that are irrelevant to our discussion, there was one in special that contained the full path and name of the output image to be generated. Although it seems a simple task, it doesn’t take too long to realize some of the challenges:   If the output image is stored with a fixed path and name, it doesn’t work well in VA reports with concurrent users: Image file names and/or locations must be unique and dynamically generated to allow concurrency, so different users don’t overwrite each other. If you generate random distinct names for each request, after the solution has been running for a while, you may have many image files stored somewhere: You must have a mechanism that manages the number of images stored, so it doesn’t grow forever. The client application consuming the images runs on a browser, so we must be able to stream the images or reference them by a URL or URI: The third-party API doesn’t accept URL or URI as parameters, so you must perform a conversion of some sort.   I’ve decided to use SAS code as a Viya Job to be able to deal with the constraints above. With the SAS code I’d be able to:   Leverage temporary files via filename statements to generate the image names – those file names are unique, and files get automatically deleted when the SAS session ends, helping with challenges 1 and 2. Leverage Viya Content Server to temporarily store the image files – files stored in the Content Server can be referenced by a URI and are automatically deleted after some time, helping with challenges 2 and 3.   I only needed to find a way to transfer the image generated by the external API to the Viya Content Server, and it only took me one Google search for “sas copy binary files” to find the solution I was looking for in the first hit, a blog by Chris Hemedinger, more precisely the portion that uses the FCOPY function from SAS:   https://blogs.sas.com/content/sasdummy/2013/09/17/copy-file-macro/   This is the SAS code with the key elements: * Close all ODS output; ods _all_ close; * Set the temporary file location; filename _bcin TEMP recfm=n; * RECFM=N means that the input is read as a stream (there are no records); * Obtain the temporary /path/file value; %let tmpfile=%qsysfunc(pathname(_bcin)); * Here you would add the code to call the external API passing &tmpfile as parameter; * Fileref _webout with name _webout.html is automatically assigned in Viya Jobs; * If fileref with name _webout.* has an extension that the browser knows how to visualize, such as html, jpg, etc. it will display it; * The pre-assigned fileref _webout is not needed, so we must delete it first; %let RC=%sysfunc(fdelete(_webout)); * Set content server file location; * We need a fileref with the image's extension (so the browser can display it) and add recfm=n; * The fileref could be called anything you want; filename _webout filesrvc parenturi="&SYS_JES_JOB_URI" name="_webout.jpg" recfm=n; * Copy API output to Content Server; data _null_; length msg $ 384; rc=fcopy('_bcin', '_webout'); if rc=0 then put 'NOTE: Copied _bcin to _webout.'; else do; msg=sysmsg(); put 'ERROR:' rc= msg=; end; run; The code starts with closing all ODS outputs because we know we will be returning an image produced by an external API, so no ODS output is needed. Then we set the temporary file name and location using a TEMP fileref called _bcin and store its value in a macro variable. That macro variable is passed to the external API as the full path and name of the output image to be created. Next, we delete the existing fileref _webout. This fileref is automatically created for you in Viya Jobs with attribute name=_webout.html. All content of filerefs with name=_webout.* are automatically streamed back to the calling application, but we need a fileref that is slightly different. We need a fileref with RECFM=N, to indicate the file consists of a stream of bytes in binary format with no record boundaries, which is going to be necessary later when using the function FCOPY to copy the image generated by the API to the Content Server, and we want its name=_webout.jpg, so the browser knows it’s an image and therefore knows how to display it. The fileref itself does not need to be called _webout. The final data step copies the output image to Viya Content Server leveraging both filerefs. For the cleanup, the temporary file given by the TEMP fileref will be automatically disposed when the job finishes, and its copy in the _webout fileref will also be automatically deleted after some time.   If for some reason you want the name attribute of the fileref to be something other than _webout.*, or send back multiple files to the calling application, then you can call the Viya Job with _action=json. This makes the job return a list of output files in a json structure and the calling application has the freedom to decide what to do with each output file. This is explored in this other SAS Communities publication called Viya Jobs that Create Multiple Output Files.   In my final implementation, the code returned a couple of files (the output image and a small json file with information about the code execution status), simply because this is the template I’m currently leveraging for the Viya Jobs I develop. It also makes it simple to integrate the job with VA via DDC (Data-Driven Content), which is what I had to do for that customer. Indeed, I used that framework to provide a solution where the chart was being created in Python, using SAS PROC PYTHON, after receiving input parameters from VA, but this will be the topic for another blog. ... View more