HeidiDT Tracker

Re: Reading json files with unicode characters

HeidiDT — Fri, 31 May 2024 12:51:37 GMT

Thank you - this worked perfectly!! Now I will discuss changing the encoding permanently with our SAS Admins.

Re: Reading json files with unicode characters

HeidiDT — Thu, 30 May 2024 11:10:32 GMT

Hi Bart

Thanks for this! I do have access tot he server via Putty, but will need help in order to run a SAS session via the command line please - I have never done that.

Re: Reading json files with unicode characters

HeidiDT — Wed, 29 May 2024 12:29:50 GMT

Thanks!

Encoding = LATIN1. We are running SAS on servers, so I don't have the ability to change that. I can of course as our admin guys ro do it, but I will have to prove that there will be no impact to our existing processes - a major undertaking. Is that the only way to do this? So specifying encoding = 'UTF-8' in the filename statement is not sufficient?

Reading json files with unicode characters

HeidiDT — Wed, 29 May 2024 12:17:57 GMT

Hi All

I have searched and searched the web for answers, but am just not winning. I am reading a json file with unicode characters that are causing the error "Some code points did not transcode.". Some of the specific values are:

- \"

- \udigits

I have tried this: filename test '/filepath/filename.json' encoding='UTF-8';
libname data JSON fileref=test ordinalcount=ALL;

but I still get the same error. Any ideas please? Unfortunately I cannot attach the file as it contains sensitive data.

Re: Importing parquet files using SAS9.4

HeidiDT — Tue, 21 May 2024 13:48:58 GMT

Thanks for the replies!! I am working on a solution to import the data to a pandas dataframe and then creating a SAS dataset (Python). Still struggling with it, but for now that is the way I am going 🙂

Importing parquet files using SAS9.4

HeidiDT — Thu, 16 May 2024 07:36:05 GMT

Hi All

One of our teams is working on data that must be used in marketing campaigns using SAS CI Studio. The output they produce is stored in parquet files. I need to import that data into SAS using SAS 9.4. Based on my research, it looks like we will need SAS/ACCESS interface to ODBC. I would just like to confirm that this is the only way before I request that (cost implication).

Many thanks!

PROC HTTP for Google docs

HeidiDT — Mon, 04 Dec 2023 13:39:15 GMT

Hi All!

I am running a very simple PROC HTTP to download an Excel spreadsheet from Google docs, and then a subsequent import step to load the data into SAS. I get a "401 Unauthorized" note in my log for the PROC HTTP, and the imported file contains the following:

Allow Google Sheets access to your necessary cookies
You won't be able to access this content if necessary cookies are turned off
Learn moreAllow cookies

Sign in to your Google Account
You must sign in to access this content
Sign in

Can't access your Google Account
We can't sign you in when the necessary cookies are turned off
Learn more

So I am obviously able to reach the site, but then need to sign in. I have searched and searched on the web for information on how to handle this, but have just been going down rabbit holes. Here is my code:

filename _httpin temp;

proc http

url="https://docs.google.com/spreadsheets/d/1sFshkC35_cY2qwinv1hNHoFjC7u965yhiHV9ga8t9lk/edit?pli=1#gid=503427334"

method="get"

out=_httpin

WEBUSERNAME="my email"

WEBPASSWORD='my google password';

debug level=3;

run;

proc import file=_httpin out=test dbms=csv replace;

getnames=no;

datarow=1;

run;

I am able to access the file via my browser, and when I submit the GET method in Postman I get the same output. Please can someone who has successfully downloaded Google docs files provide some assistance?

Thanks!

Re: SAS Data Remediation REST API

HeidiDT — Tue, 22 Aug 2023 09:24:46 GMT

Hi All!

So I have managed to get past the 500 error and am now getting a 401 (unauthorized) error. I have been trying to find documentation on the wed regarding authentication when using the SAS provided APIs, but am not having much luck. Can anyone help with authentication for SAS RESTful APIs in general, please?

Re: SAS Data Remediation REST API

HeidiDT — Fri, 18 Aug 2023 10:29:52 GMT

Hi @AlanC. I have tried with Postman and am getting the same error unfortunately.

SAS Data Remediation REST API

HeidiDT — Thu, 17 Aug 2023 11:50:59 GMT

Hi All

I am trying to POST issues to Data Remediation using the REST API, but keep getting 500 Internal Server Error. I am just trying with one issue at the moment just to get the logging sorted out, and then I will start looking at logging multiple issues. Here is my SAS Enterprise Guide code and I have attached the input file. Please could you assist if possible?

filename in "/sas/batch/sasdata/dataremediation/tables/json_manual.txt";

filename out "/sas/batch/sasdata/dataremediation/tables/json_out.txt";

proc http in=in out=out url="https://server:port/SASDataRemediation/rest/groups"

method="POST"

ct="application/json; charset=utf-8"

/* webauthdomain="LinuxAuth"*/

webusername="heididt"

webpassword="{SAS002}66B564224EE630732E6388E0395887EC37DCE27D300D953A"

;

run;

Re: Data Remediation: getting started

HeidiDT — Thu, 20 Jul 2023 06:52:42 GMT

Thanks @SASKiwi, I have reached out to SAS Education as well 🙂

Data Remediation: getting started

HeidiDT — Wed, 19 Jul 2023 06:03:38 GMT

Hi All

I am brand new to Data Remediation and need some help with getting started. Our design is as follows (steps 1-3 have already been developed, I need help with the following steps):

1. Data Quality assessment is run to identify any records that violate business rules

2. These records are written to a SQL table and surfaced in a view (v_AllRuleViolations)

3. SAS code is run to derive suggested remediation values for these rule violations, and the relevant data is written to a SQL table (Remediation)

4. Violation records, together with the suggested remediation value, are extracted from the Remediation table and sent to SAS Data Remediation

5. Workflow is instantiated:

Data Steward accepts or rejects suggested remediation
- Accept:
  - Remediation table updated (FinalRemediation=SuggestedRemediation and ProcessedDttm populated with current date and time)
  - SQL script is invoked to apply final remediation to source data
- Reject:
  - (Optional) Data Steward enters alternative remediation: populates Remediation table (AlternativeRemediation)
  - Enters reason for rejection: populates Rejection table (Rejector = username, RejectionReason = reason entered, ValueRejected=SUGGESTED and RejectedDttm=current date and time)
  - Workflow advances to next task:
    - Alerts data owner
    - Data owner may accept or reject either the suggested or alternative remediation, suggest another remediation or reject all remediation suggestions
      - Accept suggested remediation
        
        Populates Remediation table (FinalRemediation = SuggestedRemediation, ProcessedDttm = current date and time)
        
        Invokes SQL script to apply Remediation.FinalRemediation to source data
      - Accept alternative remediation
        
        Populates Remediation table (FinalRemediation = AlternativeRemediation, ProcessedDttm = current date and time)
        
        Invokes SQL script to apply Remediation.FinalRemediation to source data
      - Enter alternative remediation
        
        Populates Remediation table (FinalRemediation = newly supplied value)
        
        Invokes SQL script to apply FinalRemediation to source data
      - Reject
        
        Populates RemediationRejection table (Rejector = username, RejectionReason = reason entered, ValueRejected=ALL and RejectedDttm=current date and time)
        
        Manual intervention will be required – process tbc

If anyone is able and willing to assist me, please could you let me know? Unfortunately there is currently no Data Remediation training available, and the documentation does not provide enough guidance w.r.t. the integration between the assessment and remediation.

Thanks!

Re: Data Remediation: create new issue types from SQL tables

HeidiDT — Wed, 31 May 2023 08:19:23 GMT

Hi @SASJedi , sorry I am only replying now, got pulled off to work on other priorities. Yes, I am working with SAS® Data Remediation software

Re: SAS regular expressions: combination of ^ and $ not working

HeidiDT — Fri, 28 Apr 2023 16:11:38 GMT

EXCELLENT - thanks so much!

SAS regular expressions: combination of ^ and $ not working

HeidiDT — Fri, 28 Apr 2023 15:46:37 GMT

Hi All!

I am very new to regex - I am currently busy with an introductory course. But I have a problem where the regex works when I use it in a grep command, but when I translate it into SAS it doesn't work. Please don't leaugh at the very basic example lol, I have attached the input file and here is my code (extracting all lines that contains exactly 3 digits):

Grep command (returns the correct 3 lines):

grep -E '^[0-9]{3}$' regex18.txt

SAS code (returns 0 rows):

proc import file='/sas/batch/sasdata/users/heididt/regex/ExerciseInputFiles/regex18.txt' out=example replace;
getnames=NO;datarow=1;
run;

data example;
length var2 $8.;
set example;
var2=left(put(var1,8.));
run;

data example_out;
set example;
if prxmatch('/^[0-9]{3}$/',var2);
run;

Re: Data Remediation: create new issue types from SQL tables

HeidiDT — Wed, 05 Apr 2023 11:51:21 GMT

Hi @SASJedi and thanks for the reply. I have not created any issue types as yet, since I wanted to first find out whether it was possible to create issue types from a SQL table. I have no clue where to even start with the SAS code, since I don't know where the metadata for Data Remediation resides. I.e. I wouldn't know where to write to! I would guess the REST API would have to be used, but without having had any experience or training in Data Remediation (there are no SAS courses available so I have only gone through the User's Guide), I really have no idea where to start. I am perfectly happy creating each issue type manually and am able to do that, however I would like to be able to create the issue types dynamically / from a SQL table.

I hope this provides a bit more background?

Data Remediation: create new issue types from SQL tables

HeidiDT — Fri, 24 Mar 2023 15:13:23 GMT

I am brand new to data remediation and am still busy with the design for our data remediation processes. I am currently configuring the landscape by creating the applications, subject areas and issue types. I want to create an issue type for each field in our data - which is a lot so I don't want to do it manually as I would want to replicate it in our DEV, TEST and PROD environments and I would also like to be able to dynamically add an issue type if a new field is added. Is there a way to programmatically add issue types from SQL tables? I have googled but haven't been able to find anything yet, so any help will be greatly appreciated.

Re: SAS/SHARE: copy macro variables to remote session

HeidiDT — Mon, 27 Feb 2023 13:24:18 GMT

Thanks so much - I realised that it should be outside of the rsubmit after some more reading 🙂

Re: SAS/SHARE: copy macro variables to remote session

HeidiDT — Mon, 27 Feb 2023 12:23:41 GMT

Sorry, I made 2 glaring errors in this post. I meant SAS/CONNECT in the subject and not &SYSLOCALHOST, but &SYHOSTNAME.

SAS/SHARE: copy macro variables to remote session

HeidiDT — Mon, 27 Feb 2023 10:58:17 GMT

Hi All

I am trying to copy defined macro variables to a remote server session. I have found the code on this forum:

signon linux user="&sasbatchuser" pass="&sasbatchpass";

* Combine Credit ABT tables and download to APP server;
rsubmit;
libname rwork slibref=work server=??;

%SYSLPUT _all_;

I just have no idea what to specify in the server option of the libname statement. I tried using the value of &SYSLOCALHOST (which in my case is uatsasapp03), but got the following error:

1 libname rwork slibref=work server=uatsasapp03;
-----------
23
ERROR: Libref RWORK is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 23-7: Invalid value for the SERVER option.

Please can anyone assist?

Thanks!