topic Re: Align two report sheets in SAS Procedures

Align two report sheets

DavidD — Mon, 18 Oct 2010 17:04:25 GMT

I am a newby. I have a two page report with variables and values with columns and rows arranged one way, I have another report sheet generated with the same data with columns and values arranged in a different order.

To compare these worksheets (Both text files) What is the optimal SAS procedure? I assume I should not use PROC compare but use either transpose or some by statement?

Any suggestions.

Thanks

Re: Align two report sheets

Cynthia_sas — Mon, 18 Oct 2010 17:21:53 GMT

Hi:
I don't know what you mean by "report sheets" -- on paper??? You're going to retype the information into a SAS program??? Or, you got 2 report "files" and you want to read the files into SAS to compare the information from the 2 reports??

How were the "report sheets" originally created? Are the "report sheets" in a form that SAS can even read?? For example, if the "report sheets" are in PDF format or Word doc format, then it is unlikely that SAS could read either of these proprietary formats.

Then you say that you have "worksheets (Both text files)" -- normally, I associate the term "worksheet" with the kind of proprietary file created by a spreadsheet program, such as Excel, Lotus or Quattro Pro. Generally, a "worksheet" is NOT an ASCII text file. For example, Excel's .xls file for Excel 97-2003 is a binary workbook which can store multiple worksheet definitions, in binary form, inside the workbook definition in Microsoft proprietary form.

Without knowing more about your "report sheets" it is hard to point you to a procedure. For example -- can you open these files and read the information if you open the files in Notepad??? If the files are not "readable" in Notepad, how do you read the files?? In Excel??? If so, this might indicate that the files are stored in some proprietary format.

If the files are already in SAS format, then can you explain a bit about the files and how you need to compare them and what's in each file (in a bit more detail than "with variables and values with columns and rows arranged one way" ) Perhaps you could show an example of what the columns and rows and arrangement is??

cynthia

Re: Align two electronic file report pages

DavidD — Mon, 18 Oct 2010 19:33:20 GMT

There are two separate electronic files from two financial systems with the same (hopefully) accounting information. The information is prepared differently, however, before it is calculated and a report is generated hence I am tasked with validating that the information which is displayed in rows and columns is equal. The problem now is that the rows and columns in each file are arranged differently on the pages.

We can save both files in a format that SAS can read.

I was wondering if transpose or by would be useful programming starts for this problem.

I hope that this is an appropriate forum to bring up this matter and thank you for any assistance that you may be able to provide.

Re: Align two electronic file report pages

Cynthia_sas — Mon, 18 Oct 2010 21:40:59 GMT

Hi:
Without a better idea of how the 2 files are differently structured, it's hard to say which approach would be better. You might have to use TRANSPOSE; you might find that BY group processing would be better. For example, consider these 2 files, both with the same information, just structured differently. FILE1: [pre]
Name Bday
Alan 01/01/2007
Bob 01/01/2007
Carla 01/04/2007
[/pre]

versus FILE2:
[pre]
Day Name1 Name2
01/01/2007 Alan Bob
01/04/2007 Carla
[/pre]

Depending on how you were more comfortable writing your program, you could either transpose file 1 to compare it to file 2 or rotate/"untranspose" file 2 to compare it to file 1 ( probably doing a merge with a BY).

cynthia

Re: Align two report sheets

DavidD — Mon, 18 Oct 2010 21:43:24 GMT

Thank you.

Re: Align two report sheets

SASPhile — Tue, 19 Oct 2010 18:29:20 GMT

if you can give a layout of the variables and dummy values we can perform joins and then use proc freq.