Learning SAS? Welcome to the exclusive online community for all SAS learners.

Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Reply
New Contributor
Posts: 4

Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Hello,

 

I am having populating tables in SAS with a new Excel that I created.  I am looking to make tables/graphs/summary statistics that depict signed rank t-test results, student's t-test results, mean difference, etc...  This code worked in Virtualbox machine using a different datafile, but is not working in SAS OnDemand with my new dataset.  Currently, when I run this code all I see is output data (attached).Screen Shot 2017-08-09 at 8.23.23 PM.png 

 

Does anyone know how to remedy my error?  I've pasted the code below.

 

* proc import xlsx ;
proc import out = work.source (where= (PostTotal > 0))
datafile = "/folders/myfolders/IPE Data RC and Totals Oct 2016.xlsx"
dbms = xlsx
replace;
*mixed = yes;
getNames = yes;

sheet = "Sheet1";
run;
ods noproctitle;
ods graphics / imagemap=on;

data Work._Paired_diffs_;
set WORK.SOURCE;
_Difference_=Post1 - Pre1;
label _Difference_="Difference: Post1 - Pre1";
run;

/*** Test for normality ***/
proc univariate data=Work._Paired_diffs_ normal mu0=0;
ods select TestsForNormality;
var _Difference_;
run;

/*** t Test ***/
proc ttest data=WORK.SOURCE sides=2 h0=0 plots(showh0);
paired Post1*Pre1;
run;

/*** Nonparametric test ***/
proc univariate data=work._paired_diffs_ mu0=0;
ods select TestsForLocation;
var _Difference_;
run;
proc freq data=work._paired_diffs_;
tables post1*pre1 / nocol norow nopercent;
run;
proc means data=source;
var posttotal pretotal;
run;

proc ttest data=WORK.SOURCE sides=2 h0=0 plots(showh0);
paired Posttotal*Pretotal;

run;
/*** Clean up ***/
*proc delete data=work._paired_diffs_;
run;

Super User
Super User
Posts: 7,039

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Posted in reply to sasuser516

You need to show the SAS log. Did you get any errors?

 

Make sure to use the Insert Code icon (looks like {i} ) in the editor and paste the lines from the log into the popup box. If you paste them into the normal editor box the spacing will be corrupted.

New Contributor
Posts: 4

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

[ Edited ]

Hello! Thanks for your reply.  I've pasted the log and sas code below as you recommended.  What do you think may be going on?

 

Log

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 NOTE: ODS statements in the SAS Studio environment may disable some output features.
 60         
 61         * proc import xlsx ;
 62         proc import out = work.source (where= (PostTotal > 0))
 63                     datafile = "/home/ewilliams60/New Folder/IPE Data 2017 Sorted and Reverse Coded.xlsx"
 64                     dbms = xlsx
 65                     replace;
 66             *mixed = yes;
 67             getNames = yes;
 68         
 69             sheet = "Sheet1";
 70         run;
 
 NOTE:    Name Truncated...  Prior Experience Yes(Y) or No (N) -> Prior Experience Yes(Y) or No (N
 ERROR: Variable PostTotal is not on file WORK.SOURCE.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.02 seconds
       user cpu time       0.03 seconds
       system cpu time     0.00 seconds
       memory              1515.18k
       OS Memory           26160.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        4  Switch Count  0
       Page Faults                       0
       Page Reclaims                     633
       Page Swaps                        0
       Voluntary Context Switches        7
       Involuntary Context Switches      3
       Block Input Operations            120
       Block Output Operations           8
       
 71         ods noproctitle;
 72         ods graphics / imagemap=on;
 73         
 
 
 74         data Work._Paired_diffs_;
 75         set WORK.SOURCE;
 ERROR: File WORK.SOURCE.DATA does not exist.
 76         _Difference_=Post1 - Pre1;
 77         label _Difference_="Difference: Post1 - Pre1";
 78         run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK._PAIRED_DIFFS_ may be incomplete.  When this step was stopped there were 0 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              487.78k
       OS Memory           25508.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        5  Switch Count  2
       Page Faults                       0
       Page Reclaims                     106
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 79         
 80         /*** Test for normality ***/
 81         proc univariate data=Work._Paired_diffs_ normal mu0=0;
 82         ods select TestsForNormality;
 83         var _Difference_;
 84         run;
 
 NOTE: No observations in data set WORK._PAIRED_DIFFS_.
 NOTE: PROCEDURE UNIVARIATE used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              435.87k
       OS Memory           25508.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        6  Switch Count  1
       Page Faults                       0
       Page Reclaims                     156
       Page Swaps                        0
       Voluntary Context Switches        5
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 WARNING: Output 'TestsForNormality' was not created.  Make sure that the output object name, label, or path is spelled correctly.  
          Also, verify that the appropriate procedure options are used to produce the requested output object.  For example, verify 
          that the NOPRINT option is not used.
 
 85         
 86         /*** t Test ***/
 87         proc ttest data=WORK.SOURCE sides=2 h0=0 plots(showh0);
 ERROR: File WORK.SOURCE.DATA does not exist.
 88         paired Post1*Pre1;
 89         run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE TTEST used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              46.59k
       OS Memory           25248.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        7  Switch Count  0
       Page Faults                       0
       Page Reclaims                     34
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 90         
 91         /*** Nonparametric test ***/
 
 
 92         proc univariate data=work._paired_diffs_ mu0=0;
 93         ods select TestsForLocation;
 94         var _Difference_;
 95         run;
 
 NOTE: No observations in data set WORK._PAIRED_DIFFS_.
 NOTE: PROCEDURE UNIVARIATE used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              427.43k
       OS Memory           25508.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        8  Switch Count  1
       Page Faults                       0
       Page Reclaims                     49
       Page Swaps                        0
       Voluntary Context Switches        5
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 WARNING: Output 'TestsForLocation' was not created.  Make sure that the output object name, label, or path is spelled correctly.  
          Also, verify that the appropriate procedure options are used to produce the requested output object.  For example, verify 
          that the NOPRINT option is not used.
 
 96         proc freq data=work._paired_diffs_;
 97         tables post1*pre1 / nocol norow nopercent;
 98         run;
 
 NOTE: No observations in data set WORK._PAIRED_DIFFS_.
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              329.84k
       OS Memory           25508.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        9  Switch Count  0
       Page Faults                       0
       Page Reclaims                     84
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 99         proc means data=source;
 ERROR: File WORK.SOURCE.DATA does not exist.
 100        var posttotal pretotal;
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 101        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE MEANS used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              78.34k
       OS Memory           25248.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        10  Switch Count  0
       Page Faults                       0
       Page Reclaims                     51
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 102        
 
 
 103        proc ttest data=WORK.SOURCE sides=2 h0=0 plots(showh0);
 ERROR: File WORK.SOURCE.DATA does not exist.
 104        paired Posttotal*Pretotal;
 105        
 106        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE TTEST used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              45.46k
       OS Memory           25248.00k
       Timestamp           08/10/2017 11:43:08 PM
       Step Count                        11  Switch Count  0
       Page Faults                       0
       Page Reclaims                     9
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 107        /*** Clean up ***/
 108        *proc delete data=work._paired_diffs_;
 109        run;
 110        
 111        
 112        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 124        

 This is the code:

* proc import xlsx ;
proc import out = work.source (where= (PostTotal > 0)) 
            datafile = "/folders/myfolders/IPE Data RC and Totals Oct 2016.xlsx" 
            dbms = xlsx 
            replace;
    *mixed = yes;
    getNames = yes;

    sheet = "Sheet1";
run; 
ods noproctitle;
ods graphics / imagemap=on;

data Work._Paired_diffs_;
	set WORK.SOURCE;
	_Difference_=Post1 - Pre1;
	label _Difference_="Difference: Post1 - Pre1";
run;

/*** Test for normality ***/
proc univariate data=Work._Paired_diffs_ normal mu0=0;
	ods select TestsForNormality;
	var _Difference_;
run;

/*** t Test ***/
proc ttest data=WORK.SOURCE sides=2 h0=0 plots(showh0);
	paired Post1*Pre1;
run;

/*** Nonparametric test ***/
proc univariate data=work._paired_diffs_ mu0=0;
	ods select TestsForLocation;
	var _Difference_;
run;
proc freq data=work._paired_diffs_;
tables post1*pre1 / nocol norow nopercent;
run;
proc means data=source;
var posttotal pretotal;
run;

proc ttest data=WORK.SOURCE sides=2 h0=0 plots(showh0);
	paired Posttotal*Pretotal;
	
run;
/*** Clean up ***/
*proc delete data=work._paired_diffs_;
run;

Screen Shot 2017-08-10 at 7.48.05 PM.png

Super User
Posts: 19,789

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Posted in reply to sasuser516

@sasuser516 No attachment/log.

New Contributor
Posts: 4

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Sorry! Just attached it.
Super User
Posts: 19,789

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

My initial answer was correct, your code has errors, as noted in your log, and your file is not being read in correctly. My initial answer also had the solution to this issue so I suggest you try that. 

Super User
Super User
Posts: 7,039

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Posted in reply to sasuser516

Check if a change in the setting of VALIDVARNAME system option is what is causing the variable to not be found. Other possibility is that PROC IMPORT has changed or the XLSX file is different.

 

Super User
Posts: 19,789

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Posted in reply to sasuser516

I thought the path to the file was different in Academics On Demand. 

Verify that your import is working, I suspect it didn't. 

 

You can find the path to your file by right clicking it, selecting properties and checking the path. 

New Contributor
Posts: 4

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Thank you! I changed the path to the correct path based on your direction.  I now have a log with a series of errors, which I've pasted in the reply above.  What do you think may be going on?

 

Thanks!

Eva

Super User
Posts: 19,789

Re: Problem with Populating Tables in SAS OnDemand using Previously Functional SAS Code

Posted in reply to sasuser516

@sasuser516 please don't edit posts in such a manner, it makes it difficult to follow the conversation.  You still have errors in importing the data. Not sure why, but fixing those will likely fix the majority of your issues. Excel isn't a database and doesn't have types or defined column. This allows you the flexibility in spreadsheets but isn't great for storing or collecting data. PROC IMPORT and SAS have to make guess at types and column names. It's possible how this happens may differ on different systems due to the fact that it's really an educated guess, but still a guess and that guess is based on options (guessingrow, validvarname), SAS versions, Excel versions and the underlying operating system. As such, Excel files are a pain in the a** when it comes to importing data. So it means testing the import...thoroughly to ensure you get what you want. A common workaround is to convert the file to a CSV and then you can specify the types more definitively and not have this issue. You may still have issues moving the code to different systems due to file encoding issues though. This assumes that you're trying to develop code that will work on different systems.

 

 

Ask a Question
Discussion stats
  • 9 replies
  • 301 views
  • 0 likes
  • 3 in conversation