About the Data Quality solution

[jorgealem]

To the salesforce consultors...

My question is: can I take into DATA MANAGEMENT STUDIO several files and standardize them running the same job ????

I mean, develop an automatically job that takes any file and standardize it

And make the job running 1 day at week (automatically) 

Thank you so much and greetings.

[Reeza]

---

@jorgealem wrote:

To the salesforce consultors...

My question is: can I take into DATA MANAGEMENT STUDIO several files and standardize them running the same job ????

I mean, develop an automatically job that takes any file and standardize it

And make the job running 1 day at week (automatically) 

Thank you so much and greetings.

---

Depends on what you mean by 'any file' and 'standardize'

Can you please expand on those to ensure we can more accurately answer your question.

[jorgealem]

Hi Reeza, thank you for your help.

I have a folder called "FOLDER" in Windows. There, an user puts a file each week (the user deletes the past file... and puts the new) and I want to know if SAS DATA MANAGEMENT STUDIO can run the job automatically when the user puts the file on "FOLDER".

The idea is ... 

Data Management Studio will execute a serie of data standardization ... on a file in "FOLDER" folder. The folder will be updated manually every week, and I want to know if SAS can detect if there is a new file and running the jobs on that new file.

[Reeza · Accepted solution]

It can definitely detect a new file and run the process on it, as long as that file structure is consistent and the rules are consistent this a standard ETL process. It cannot take a brand new file and know to standardize your variables based on field names automatically. But for a specific process, you build a flow diagram and schedule it or set up a trigger for it to run. I'm not sure how the scheduling aspect works but I know there are mechanisms for it.

[jorgealem]

When I say "standardize" I mean ...

If the Contact field in the data file brings me "351-6236182", SAS will transform it to "+5493516236182"

[ballardw]

---

@jorgealem wrote:

When I say "standardize" I mean ...

If the Contact field in the data file brings me "351-6236182", SAS will transform it to "+5493516236182"

---

Do you have list of the possible Contact Field values and know what all of then should transform too? Of does this process have to identify previously unknown values and do something special?

An INFORMAT is one way to read a known list values and store them as something else but may be cumbersome if there is a lot of churn in the values. I am also not sure if your processes support custom informats but that is one way. Another is to have a table with the start/end values and do a look up and replace.

And example with informat that uses the "other", meaning not a known or expected value, to assign an error condition so a message is written to the log about unexpected value and no value is written to the data set for that variable.

Proc format library=work;
invalue $contact
"351-6236182" = "+5493516236182"
"John"        = "+5493516239999"
other         = _error_
;
run;

data example;
   informat contact $contact20.;
   input contact;
datalines;
351-6236182
John      
Fred
;
run;

And if you have data sets of the values formats could be created from them.

[SASKiwi]

If you are referring to SAS Data Quality, and the tools it has for standardizing data then yes. It depends if you are licensed for these though.

Edit: Just seen your phone number example. SAS DQ has built-in methods for that, no coding required.

[jorgealem]

Thank you so much for all your replies !

They have been helpful.

Greetings.