topic Re: file formats in SAS Programming

file formats

don21 — Mon, 19 Mar 2018 13:36:19 GMT

Dear all,

I have been struggling with the below file issue. My project is ready to get deployed next week and now we started facing the below issues wih the code.

We receive .XLS and .XLSX files from various countries, which looks like attached. Once imported, the default column names (A B C D.... or F1 F2 F3....) will be assigned and then we coded the rest accordingly. till now everything was going good, lately when we import the file, the code is createing an extra column in the first place of the file which is moving the rest of the columns to right thus F1 becomes F2 so on.. and because of this the code is picking incorrect data.

I dont understand how this can happen. Please help me understand and kindly advise on solution for this..

Thank you!

Re: file formats

PaigeMiller — Mon, 19 Mar 2018 14:06:02 GMT

I won't look at the attached .xlsx file, because downloading and opening .xlsx files are security risk. However, the question I have for you is: did you actually look at the .xlsx file that is the problem? What is in column A? Is it the expected data, or is it the unexpected extra column? Can you show us a screen capture of what the first few rows and columns of the .xlsx file which is causing the problem?

Long-term, this is a problem using .xlsx files to transmit the data, that the format of the file can change and then your code won't work.

Re: file formats

ballardw — Mon, 19 Mar 2018 14:09:23 GMT

@don21 wrote:

Dear all,

I have been struggling with the below file issue. My project is ready to get deployed next week and now we started facing the below issues wih the code.

We receive .XLS and .XLSX files from various countries, which looks like attached. Once imported, the default column names (A B C D.... or F1 F2 F3....) will be assigned and then we coded the rest accordingly. till now everything was going good, lately when we import the file, the code is createing an extra column in the first place of the file which is moving the rest of the columns to right thus F1 becomes F2 so on.. and because of this the code is picking incorrect data.

I dont understand how this can happen. Please help me understand and kindly advise on solution for this..

Thank you!

So show the code that is reading the .xls and/or .xlsx files. That statement alone raises concerns because the file structures are different and have different limitations.

My guess without an example is that you are 1) using proc import and 2) one or more of the data providers changed the file layout without telling you.

Re: file formats

Kurt_Bremser — Mon, 19 Mar 2018 14:17:23 GMT

First of all, stop using Excel files and only accept text-based files, either fixed-width or csv.

Second, stop using Excel files and only accept text-based files, either fixed-width or csv.

Third, stop using Excel files and only accept text-based files, either fixed-width or csv.

As long as you accept garbage, the stink won't go away.

Re: file formats

Reeza — Mon, 19 Mar 2018 14:42:23 GMT

This means the Excel files do not have the same structure. Since Excel is not a database it does’t enforce types.

This willl definitely be problematic in a production type process.

You should convert the file to a CSV and read that in instead. You can control the types/formats as desired and ensure you always have the same data inputted or at least know if you don’t.

Re: file formats

don21 — Tue, 20 Mar 2018 10:01:27 GMT

Hi KurtBremser,

I agree to what you said, unfortunately I don't have the authority to reject garbage. No matter what, I have to automated the crap that I get. With a lot of patience, I did but now this? Please advise.

Regards..

Re: file formats

don21 — Tue, 20 Mar 2018 10:24:48 GMT

Hi Reeza,

I receve data for 14 countries. Each country sends atleast 20 excel files (both .xls and .xlsx), If I have to change the codes to CSV conversions at this point, it wont be feasible and lot of things can go wrong while doing this.

Also, the structure of the input files loosk exactly the same including the blank columns. The code itself is creating a blank column sometimes.

I understand that working with excel files is complete wate of time but at this point where my project is ready to go live, I need some solution to fix this..

Please help!

Re: file formats

Kurt_Bremser — Tue, 20 Mar 2018 10:55:22 GMT

@don21 wrote:
Hi KurtBremser,

I agree to what you said, unfortunately I don't have the authority to reject garbage. No matter what, I have to automated the crap that I get. With a lot of patience, I did but now this? Please advise.

Regards..

You have a fundamentally broken process. That needs to be fixed first.

I couldn't maintain 1000+ SAS jobs on my own (which I do) without proper processes.

Make it clear to the people responsible that keeping that mess up will cost MONEY in terms of your unnecessarily wasted time, as you will have to go on fixing and fixing it time and again. While you could do really useful stuff.

Invest the time NOW to set up a proper process (sensible file format, documented structure, agreement between provider and receiver). It will be worth it, a hundred times over. Not exagerrating here.

I really mean it. You can quote me on this. And @Reeza probably too.

Re: file formats

andreas_lds — Tue, 20 Mar 2018 12:23:53 GMT

@don21 wrote:

I understand that working with excel files is complete waste of time but at this point where my project is ready to go live, I need some solution to fix this..

If you code does not contain steps to detect and fix excel-files not having the expected structure, your project is not ready to go live. You have to check every variable and ensure that it has the expected type and length.

Can you post the code used to read the excel files?

Re: file formats

Reeza — Tue, 20 Mar 2018 15:24:41 GMT

Unfortunately converting to ~~XLSX~~ CSV is the best solution. You could add a check to ensure your data meets the expected format but IMO that’s more work.

There are macros row and scripts that will convert all your XLSX and XLSX to CSV in batch.

Ultimately however, it’s your choice which approach to take. Do consider what happens if you go with the Excel approach and and it decides to add two extra columns next time or a bunch of empty rows. Is your process robust enough to handle that?

EDIT: I typed XLSX when I meant CSV.

The script is here and the code is 6 to create a CSV (51 for XLSX in the code).

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel

 options noxwait noxsync; 
 
%macro convert_files(default=,ext=);
 
data _null_;
file "'&default\temp.vbs'";
put "set xlapp = CreateObject(""Excel.Application"")";
put "set fso = CreateObject(""scripting.filesystemobject"")";
put "set myfolder = fso.GetFolder(""&default"")";
put "set myfiles = myfolder.Files";
put "xlapp.DisplayAlerts = False";
put " ";
put "for each f in myfiles";
put "  ExtName = fso.GetExtensionName(f)";
put "  Filename= fso.GetBaseName(f)";
put "    if ExtName=""&ext"" then";
put "           set mybook = xlapp.Workbooks.Open(f.Path)"; 
put "           xlapp.Visible = false";
put "           mybook.SaveAs ""&default.\"" & Filename & "".csv"", 6";
put "    End If";
put "  Next";
put "  mybook.Close";
put "  xlapp.DisplayAlerts = True";
/* Removes original files */
/*put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";*/
put " xlapp.Quit";
put " Set xlapp = Nothing";
put " strScript = Wscript.ScriptFullName";
put " FSO.DeleteFile(strScript)"; 
run; 
 
x "cscript ""&default\temp.vbs""";
 
%mend;

Source:

http://support.sas.com/kb/43/496.html

Re: file formats

ballardw — Tue, 20 Mar 2018 19:28:51 GMT

@don21 wrote:
Hi Reeza,

I receve data for 14 countries. Each country sends atleast 20 excel files (both .xls and .xlsx), If I have to change the codes to CSV conversions at this point, it wont be feasible and lot of things can go wrong while doing this.

Also, the structure of the input files loosk exactly the same including the blank columns. The code itself is creating a blank column sometimes.

I understand that working with excel files is complete wate of time but at this point where my project is ready to go live, I need some solution to fix this..

Please help!

It is real hard to fix code without seeing any code. No code, no fix to code. Simple solution set.

Re: file formats

don21 — Fri, 23 Mar 2018 04:47:01 GMT

Hi Ballardw,

PFB sample example code that we used:

option obs=max;
proc import datafile= "//<<<Path>>/ABCD.xlsx"
out=ABCD
dbms=xlsx replace;
run;

DATA ABCD1;
SET ABCD ( KEEP = A B H I J P Q R S U); /** after renaming the A B... variables, they will be dropped at the end*/
<<>>
<<>>
run;

Re: file formats

don21 — Fri, 23 Mar 2018 04:52:04 GMT

@andreas_lds @Andre

Hi, We have checked every possibility that could go wrong, I have taken over this project recently and the test runs went well t=until now. Now a days the input file formats have been creating issues although they look as as the previous ones. we could not get back tot he providers as we could not spot the difference between the files.. SAS is creating blank columns in between is my best guess in this situation or there must be some internal format issue with the input file which we couldnt spot.

Re: file formats

don21 — Fri, 23 Mar 2018 05:00:04 GMT

option obs=max;
proc import datafile= "//<<path>>/ABCD.xlsx"
out=ABCD
dbms=xlsx replace;
run;

DATA ABCD1;
FORMAT Current_Excess BEST.;
SET ABCD ( KEEP = A B H I J P Q R S U);
RETAIN RM_NAME Entity_Name RM_CODE Limit_Product_Type Account_ReferenceNo System_ID;
IF INDEX (A, "Name :")>0 THEN RNAME = B;
IF INDEX (A, "Customer :")>0 THEN Entity = B;
IF INDEX (A, "Code :")>0 THEN CODE = B;
IF INDEX (A,"Product :") THEN Limit_Product = B;
IF INDEX (A,"Id :") THEN ReferenceNo = B;
IF INDEX (A,"Id :") THEN ID = B;
CURRENCY =P;
Current_Excess = Q;
/*Current_Excess = INPUT (Current_Exces,BEST.);*/
NO_OF_DAYS_InExcess =U ;
Excess_Sin = S;
CODE = I;
NAME = J;
EXCESS_SINCE = INPUT (EXCESS_SIN, COMMA8.);
EXCESS_SINCE = EXCESS_SINCE - 21916;
FORMAT EXCESS_SINCE DATE11.;
LENGTH FILENAME $50. REPORT $30.;
FILENAME = "ABCD";
REPORT = "ABCD";
IF CURRENCY IN (" ", "Fin Ccy") THEN DELETE;
DROP A B H I J S P Q R U EXCESS_SIN;
RUN;

PROC SORT DATA = ABCD1 OUT = ABCD_FINAL noduprecs;
BY NAME;
RUN;

Sample code for a file....

Re: file formats

Reeza — Fri, 23 Mar 2018 20:44:46 GMT

Usually, in my experience, this happens if you have formulas or had information and deleted but had a format attached. Excel is still keeping that information somewhere so SAS thinks it has info.

If you data is always in specific columns though, you could specify the range start and column end and see if that alone fixes it.

Re: file formats

Tom — Fri, 23 Mar 2018 20:51:36 GMT

@don21 wrote:

Hi Ballardw,

PFB sample example code that we used:

option obs=max;
proc import datafile= "//<<<Path>>/ABCD.xlsx"
out=ABCD
dbms=xlsx replace;
run;

DATA ABCD1;
SET ABCD ( KEEP = A B H I J P Q R S U); /** after renaming the A B... variables, they will be dropped at the end*/
<<>>
<<>>
run;

Your PROC IMPORT code is telling SAS to read the variable names from the first row in the file. So if someone changes the first row then the variable names will change. You could tell SAS not to read the variable names.

Your PROC IMPORT code is not telling SAS which sheet to read from the workbook. What happens if they send one workbookwith three sheets?

Re: file formats

Patrick — Tue, 27 Mar 2018 10:39:04 GMT

@don21

Like others already wrote Excel is unsuitable for data exchange. But I also understand that this might be out of your control. You will just need to be very clear with your client/manager that there is quite a big likelihood for ongoing production issues with such source data.

Comparing the sample Excel with your code demonstrates one of the issues: In your code you're reading NO_OF_DAYS_InExcess from column U while in your sample Excel it's on column V.

When dealing with Excels and especially when the sheet actually contains reports and is not just a data sheet, you need to code with much more checks and balances than what your code sample demonstrates.

Don't trust on which column a report starts. Analyse the "raw" data first to determine the structure (i.e. search on which column you find a token like "Client ID" - just something which is unique and gives you the starting point.

Client ID should also give you the left top corner of a table. So next step for me would be to analyse the table headings on this row and determine if they follow an expected structure. Only if that's validated I'd go and map the data to my output columns.

Basically: You need much more analysis of the source structure and you need much more validation than what the code you've posted demonstrates.

I've been once in a similar situation. My approach was to first convert the Excels to .CSV as @Reeza proposes, then implement all the checks and balances and create a .csv structure as I'd really would have needed it as a data source. After this step I could go for a clean ETL process.

This allowed me to separate "the mess" from the rest of the process and as I've been optimistic also communicate back to the client what data exchange format they should aim for in a future release (and then hoping the only change required to my code could be to remove the "messy" conversion step). ....I believe this never happened but it certainly helped operationally to separate issues with unreliable data sources from the rest of the process

Re: file formats

don21 — Tue, 27 Mar 2018 08:59:53 GMT

Sure, Thank you Patric.. I requested my clients if the could give the inputs in any other formats.. They suggested .RPT. Well, I have never used that before, tried to google for syntax but no use. Any views on how to do that? because, if this is good, atleast I can buy sometime and do the modifications in my project.

Many Thanks..

Re: file formats

andreas_lds — Tue, 27 Mar 2018 09:18:06 GMT

"An RPT file is a report or output file created by Crystal Reports" (https://fileinfo.com/extension/rpt)

This file-format is even worse than Excel.

Re: file formats

Patrick — Tue, 27 Mar 2018 11:14:53 GMT

@don21

In the end of the day your client is the king and if you can help them to shape their data into a form where they can get value out of it then this is your job. This is clearly a business driven project with people in it who don't have too much understanding of data organization. But that's likely why they've engaged with you. You're the expert and you can do it for them.

The one thing you probably should do to protect yourself: Write down some sort of interface specification (not IT style but made up business style) where you define the few elements which are essential for your code to work (like sheet name, the name of the "tokens" you use in your code to find the starting point of data, and the name of the column headings) - and have your customer confirm that they can deliver on this. This won't only you, it will also help your customer to understand what they have to pay attention to.

The comparable (forecasting) project I've been in: It was ugly from an ETL perspective. Quite a few of the Excel extracts where actually SAP reports and accessing SAP directly would have been the clean way to go. But that was just not possible in the context (SAP owned by HQ, project run by a country agency). ...But then: Once I was able to create a "clean data warehouse" my customer was able to add that much value to the available data (much better forecasts) that the approach went global (big multinational) and then of course things got built much cleaner. I hadn't really been involved in this stage anymore but still: What started as a big ugly mess ended as something which I consider as one of the successes in my professional career.