Sensory Computer Systems Forums Index Sensory Computer Systems Forums
Welcome to the Sensory Computer Systems Online Forums!
 
 FAQFAQ   SearchSearch   RegisterRegister 
 ProfileProfile   Log inLog in   
   Sensory Computer Systems Home   

Using Excel to Modify a Test Result Data File

 
Post new topic   Reply to topic    Sensory Computer Systems Forums Index -> Reporting and Analysis
View previous topic :: View next topic  
Author Message
PAL



Joined: Jun 01 2005
Posts: 24

PostPosted: Tue Jun 14, 2005 3:19 pm    Post subject: Using Excel to Modify a Test Result Data File Reply with quote

Edit: This is not related to permanently changing data in your SIMS database. This is about modifying the data set DAT file passed to SAS for an individual stats run.

If you need to edit a large .DAT file, it can be difficult to make your changes accurately when you are using a text editor, because each line looks nearly the same. As an alternative you can:

1. Export Raw Data to Excel.
2. Make the changes using Excel editing features.
3. Save the Excel spreadsheet to a .CSV (comma-delimited) file.
4. Copy the .CSV file to replace the existing mytest.DAT file previously created by the SIMS 2000 Statistical Analysis module.
5. Modify the .DAT file so it can be can be read properly by SAS and by Excel itself through the SIMS 2000 Statistical Analysis.
6. If necessary, make changes to the associated SAS script (.SAS) or Excel Export Info File (.EXL).
7. Resubmit the .SAS script or .EXL Excel Export Info File for analysis.

Each of these steps is discussed in detail below.

1. Export Raw Data to Excel.

1a. From SIMS 2000 select Reports from the Pull Down Menu.
1b. Select Export SIMS 2000 Raw Data.
1c. Select the desired Affective/Descriptive Test Result Code.
1d. Click the Column Options Tab.
1e. Select the options as shown in the figure below. These are the basic defaults for exporting data.

Click Here for FULL sized image


1f. Click the Link to Panelist Demographic Data Tab.
1g. Uncheck, if necessary, the box Include Columns for Panelist Demographic Information.
1h. Click the Prepare Data Tab.
1i. Click Prepare Data for Export.
1j. Click the Excel Tab.
1k. Choose either Use Direct Export or Use CSV Text File as Excel Data Source.
Tip: Use CSV Text File will be faster with a large data set.
1l. Click Export to Excel.
1m. Excel will open a spreadsheet with column headings and test result data.


2. Make the changes using Excel editing features.

A spreadsheet is a natural tool for the editing of rows and columns of numbers. There are two features of Excel that should come in handy for the edits that you can expect to make.

Highlight all your data and column headings, then:

2a. From the Data menu select Sort.
Use this feature to put all the data you want to edit in a limited area of the spreadsheet. For example, you may want to concentrate on data points for a single panelist, or for a particular sample and rep. Sorting data makes it easier to find and manipulate the data you want to change.

2b. From the Edit menu select Replace.
Using the Find and Replace feature, you can restrict the changes by highlight a limited area of the spreadsheet. Specify the characters you want to find and replace. Click Replace All. For example, the Find and Replace operation below would replace all instances of the number 1 with 5 in the highlighted area of the spreadsheet.




3. Save the Excel spreadsheet to a .CSV (comma-delimited) file.

If you plan to use the .CSV file again to do additional data edits, then we recommend that you retain the column headings in the spreadsheet. If not, you can delete the heading row before saving. In addition, you should check that there are no blank rows.

3a. From the Excel File menu select Save As....
3b. For Save as type choose CSV (Comma delimited) (*.csv).
3c. You may see messages like the ones below. Click OK or Yes in response to each of them.






4. Copy the .CSV file to replace the existing mytest.DAT file previously created by the SIMS 2000 Statistical Analysis module.

There are several ways to do this. Using Windows Explorer follow these steps:

4a. Highlight the mytest.CSV file you just created.
4b. Copy the file to the Windows Clipboard. Either Hold the Ctrl key and press the letter C, or From the Edit menu, select Copy.

4c. Delete the mytest.DAT file from the SIMS 2000 Data Directory. Highlight the mytest.DAT file and press the Delete key.

4d. Copy the mytest.CSV file to the SIMS 2000 Data Directory. Hold the Ctrl key and press the letter V, or From the Edit menu, select Paste. Highlight the file you just copied to the directory. It has the name Copy of mytest.CSV. Right-click the file and select Rename from the menu. Change the name to mytest.DAT. Press the Enter key to complete the file rename procedure.

Click Yes in response to the prompt below:



5. Modify the .DAT file so it can be can be read properly by SAS and by Excel itself through the SIMS 2000 Statistical Analysis.

5a. From the Statistical Analysis form in SIMS 2000, click the View Analysis Files tab.
5b. Choose Data File (DAT) then Select File.
5c. Highlight the .DAT file you want to work with, then click Open.

The new .DAT file now looks like this:
Rep,Block,Judge,Sample,Order,Liking,Liking2
1,1,0000000003,1,1,5,.
1,1,0000000003,2,2,.,4
1,3,0000090001,2,1,4,.
1,3,0000090001,1,2,.,5

Note that all the data is just separated by commas without the quote marks. If necessary, edit the .DAT file to eliminate blank rows and the heading row. In this example, you must delete the first row of column labels.

5d. From the SIMS 2000 menu, click Save.
5e. Save the file with the same name (mytest.DAT, or whatever your name is).
5f. Click Exit.


6. If necessary, make changes to the associated SAS script (.SAS) or Excel Export Info File (.EXL).

When the associated file is a .SAS script, you may have to modify these lines when working with an Affective or Descriptive Test:

  • Block Type
  • # Samples Presented
  • Lambda Prime
    -------------------------
  • Lines associated with PROC GLM


When the associated file is an .EXL file, the lines above are ignored because the analysis performed is always one-way analysis on Sample.

For both associated file types (.SAS and .EXL) you may have to modify the number and content of the Sample Descriptions.

To modify an existing .SAS or .EXL file follow these steps:

  1. Click the SIMS 2000 Statistical Analysis icon.
  2. Click the View Analysis Files Tab.
  3. Select either SAS or EXL File Type as desired.
  4. Click Select File.
  5. Highlight the desired file name, then click Open.
  6. Edit the file as a text file.
  7. When done, either

    • Click Save to save the file, or
    • Click Submit to save the file AND to submit it for SAS or Excel analysis, or
    • Click Exit to cancel and discard changes made.


Let's look at an example.

The first few lines of the .SAS or .EXL file may look like this:

/* ---------------------------
SIMS2000 Export and Analysis: created by SIMS2000 at 03:22 PM on Jun 10 2005

Result File Name...: EXAMPLE1A
Exported by (Owner): DEMO
Questionnaire Code.: EXAMPLE1A
Experimental Design: EXAMPLE1A
Block Type.........: Complete
Panel Group Code...: Use the Entire Panelist Population
Test Definition....: EXAMPLE1A

Sample Descriptions:
1: Control
2: Experiment #1
3: Experiment #2

Attribute Descriptions:
X1: SWEETNESS (Line Scale)
X2: SOURNESS (Line Scale)
.... (attribute listing continued)

When the Block Type is Incomplete, there are two additional lines required just below Block Type line:

# Samples Presented: 2 (equal to Number Presented (K) in the Experimental Plan)
Lambda Prime.......: 2 (equal to Lambda in Experimental Plan multiplied by # of times the set of blocks is repeated in the test)

When the Block Type is Complete, the above two lines, if present, are properly ignored.

Whenever you modify a .DAT file, you may also need to adjust the following sections of the associated .SAS script file:

  1. Block Type: Complete or Incomplete
  2. # Samples Presented (ignored when Block Type = Complete)
  3. Lambda Prime (also ignored when Block Type = Complete)
  4. Lines associated with SAS Procedure PROC GLM


Block Type determines the type of analysis that SAS will run. For Complete Block Type, SAS can run GLM Analysis Procedure with either Duncan or Tukey Analysis of the Sample Mean Separation. This will normally be a two-way analysis on Judge (Panelist) and Sample.

For Incomplete Block Analysis, the GLM Analysis employs a two-way analysis on Block Number and Sample. For an Incomplete Block Analysis, it is generally better if each sample is seen with each of the other samples the same number of times.

For example, you may combine two tests of four samples. Each test presents only two of the four samples. The Experimental Design of each test uses the same Experimental Plan. The panelist list in each of the two tests is identical. Each panelist sees two samples in the first test, and two different samples in the second test.

So the combination of the two tests together actually changes the Block Type from Incomplete to Complete. Modify the .SAS file to change the Block Type to Complete. In addition you should modify the PROC GLM so it is appropriate for a Complete Block analysis. The PROC GLM appears near the bottom of the .SAS script file. For an Incomplete Block type the GLM procedure lines look something like this:

PROC GLM;
CLASS BLOCK SAMPLE;
MODEL X1-X2 = BLOCK SAMPLE;
LSMEANS SAMPLE / ALPHA=0.0500;

Replace the lines above with these:

PROC GLM;
CLASS JUDGE SAMPLE;
MODEL X1-X2 = JUDGE SAMPLE;
MEANS SAMPLE / DUNCAN ALPHA=0.0500;

The last line calls the Duncan's Multiple Range Test to evaluate the separation of Sample Means. Substitute that line with
MEANS SAMPLE / TUKEY ALPHA=0.0500;
to use the Tukey's Studentized Range (HSD) Test instead.

7. Resubmit the .SAS script or .EXL Excel Export Info File for analysis.
Back to top
View user's profile
Display posts from previous:   
Post new topic   Reply to topic    Sensory Computer Systems Forums Index -> Reporting and Analysis All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group