I getting errors when I am running attached program. Could any one please help me to find the what is the wrong with my attached program?
Hello @Balasooriya
The issue appears to be with the import part of your code. Try the following and let us know the outcome
FILENAME REFFILE 'D:\Acadamic\MS plant breeding\4th semestor\Dr Hettiarachchi\trial2.xlsx';
PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.try1 replace;
GETNAMES=YES;
RUN;
Once this is done, the rest of code should work.
Please describe in words what you are trying to do and what problem you are having.
Please copy the lines from the SAS log of the step that is causing your trouble and paste it into your message (not as an attachment) by clicking on the Insert Code button and pasting it into the pop-up window. Using the Insert Code button will preserve the character positions of the text in the LOG.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
NOTE: ODS statements in the SAS Studio environment may disable some output features.
73
74 /** Import an XLSX file. **/
75
76 proc import file='D:\Acadamic\MS plant breeding\4th semestor\Dr Hettiarachchi\trial2.xlsx'
77 out=try1
78 dbms=excel replace ;
ERROR: DBMS type EXCEL not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 195.12k
OS Memory 33444.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 61 Switch Count 0
Page Faults 0
Page Reclaims 22
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
79 run ;
80 proc prinqual data=try1 out=try2 n=4 replace mdpref;
ERROR: File WORK.TRY1.DATA does not exist.
81 transform identity (avr dfl)
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
82 monotone (rc cc);
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
83 id genotype ;
ERROR: No data set open to look up variables.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TRY2 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.TRY2 was not replaced because this step was stopped.
NOTE: PROCEDURE PRINQUAL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 349.09k
OS Memory 33444.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 62 Switch Count 0
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 16
84 Data try3;
85 set try1;
ERROR: File WORK.TRY1.DATA does not exist.
86 keep genotype block;
WARNING: The variable genotype in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable block in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TRY3 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.TRY3 was not replaced because this step was stopped.
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 536.34k
OS Memory 33700.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 63 Switch Count 0
Page Faults 0
Page Reclaims 65
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
87 proc sort;
88 by genotype;
ERROR: Variable GENOTYPE not found.
89 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 442.03k
OS Memory 33960.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 64 Switch Count 0
Page Faults 0
Page Reclaims 50
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
90 Data try4;
91 if (_type_="SCORE")then output try4;
92 set try2;
NOTE: Variable _type_ is uninitialized.
NOTE: There were 0 observations read from the data set WORK.TRY2.
NOTE: The data set WORK.TRY4 has 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 839.75k
OS Memory 33960.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 65 Switch Count 2
Page Faults 0
Page Reclaims 111
Page Swaps 0
Voluntary Context Switches 10
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264
93 proc sort;
94 by genotype;
ERROR: Variable GENOTYPE not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 434.90k
OS Memory 33960.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 66 Switch Count 0
Page Faults 0
Page Reclaims 49
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
95 data try5;
96 merge try3 try4;
97 by genotype;
98 run;
ERROR: BY variable genotype is not on input data set WORK.TRY3.
ERROR: BY variable genotype is not on input data set WORK.TRY4.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TRY5 may be incomplete. When this step was stopped there were 0 observations and 1 variables.
WARNING: Data set WORK.TRY5 was not replaced because this step was stopped.
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 1076.34k
OS Memory 34220.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 67 Switch Count 0
Page Faults 0
Page Reclaims 95
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
99 DATA try6;
100 set try5;
101 if(genotype>599) then new=0;
102 else new=1;
103 if(new) then genoc=999;
104 else genoc=genotype;
105 RUN;
NOTE: Variable genotype is uninitialized.
NOTE: There were 0 observations read from the data set WORK.TRY5.
NOTE: The data set WORK.TRY6 has 0 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 950.87k
OS Memory 34220.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 68 Switch Count 2
Page Faults 0
Page Reclaims 124
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264
106
107 PROC GLM data=try6 outstat=stat2;
108 ods output ParameterEstimates=Parms1;
ERROR: Variable BLOCK not found.
NOTE: The previous statement has been deleted.
ERROR: Variable AVR not found.
ERROR: Variable DFL not found.
ERROR: Variable RC not found.
ERROR: Variable CC not found.
NOTE: The previous statement has been deleted.
ERROR: Variable block not found.
NOTE: The previous statement has been deleted.
109 CLASS block genotype genoc;
110 MODEL avrdfl rc cc = block genoc genotype*new/solution;
111 random block;
112
113 RUN;
114
115 /** **/
NOTE: The data set WORK.STAT2 has 0 observations and 0 variables.
WARNING: Data set WORK.STAT2 was not replaced because new file is incomplete.
NOTE: PROCEDURE GLM used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 461.12k
OS Memory 33960.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 69 Switch Count 1
Page Faults 0
Page Reclaims 48
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
WARNING: Output 'ParameterEstimates' 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.
WARNING: The current ODS SELECT/EXCLUDE/OUTPUT statement was cleared because the end of a procedure step was detected. Probable
causes for this include the non-termination of an interactive procedure (type quit; to end the procedure) and a run group
with no output.
116 Data avr(rename= Estimate=avr) dfl(rename= Estimate=dfl)
117 rc(rename= Estimate=rc) cc(rename= Estimate=cc) ;
118 set parms1;
ERROR: File WORK.PARMS1.DATA does not exist.
119 if (Dependent="avr")then output avr;
120 if (Dependent="dfl")then output dfl;
121 if (Dependent="rc")then output rc;
122 if (Dependent="cc")then output cc;
123
124 keep parameter estimate ;
125 run;
WARNING: The variable Estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable parameter in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable parameter in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable parameter in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Estimate in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable parameter in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable estimate in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.AVR may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.AVR was not replaced because this step was stopped.
WARNING: The data set WORK.DFL may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.DFL was not replaced because this step was stopped.
WARNING: The data set WORK.RC may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.RC was not replaced because this step was stopped.
WARNING: The data set WORK.CC may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.CC was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 1062.93k
OS Memory 34468.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 70 Switch Count 0
Page Faults 0
Page Reclaims 87
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 32
126
127 proc sort Data= avr;
128 by parameter;
ERROR: Variable PARAMETER not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 332.90k
OS Memory 34468.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 71 Switch Count 0
Page Faults 0
Page Reclaims 15
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
129 proc sort Data= dfl;
130 by parameter;
ERROR: Variable PARAMETER not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 337.56k
OS Memory 34468.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 72 Switch Count 0
Page Faults 0
Page Reclaims 15
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
131 proc sort Data= rc;
132 by parameter;
ERROR: Variable PARAMETER not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 330.43k
OS Memory 34468.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 73 Switch Count 0
Page Faults 0
Page Reclaims 15
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
133 proc sort Data= cc;
134 by parameter;
ERROR: Variable PARAMETER not found.
135
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 344.68k
OS Memory 34468.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 74 Switch Count 0
Page Faults 0
Page Reclaims 15
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
136 data means1;
137 merge avr dfl rc cc ;
138 by Parameter;
139 run;
ERROR: BY variable Parameter is not on input data set WORK.AVR.
ERROR: BY variable Parameter is not on input data set WORK.DFL.
ERROR: BY variable Parameter is not on input data set WORK.RC.
ERROR: BY variable Parameter is not on input data set WORK.CC.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MEANS1 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.MEANS1 was not replaced because this step was stopped.
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 1444.84k
OS Memory 35236.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 75 Switch Count 0
Page Faults 0
Page Reclaims 102
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 16
140
141 /** CLUSTER ANALYSIS **/
142
143 Data means1 (rename= parameter=genotype);set means1 (firstobs=8);
WARNING: FIRSTOBS option > number of observations in WORK.MEANS1.
144
WARNING: The variable parameter in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: There were 0 observations read from the data set WORK.MEANS1.
NOTE: The data set WORK.MEANS1 has 0 observations and 0 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 744.53k
OS Memory 34980.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 76 Switch Count 2
Page Faults 0
Page Reclaims 26
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 160
145 proc distance data=means1 out=tomDist1 method=Euclid;
146 var interval(avr dfl rc cc / std=std );
ERROR: Variable AVR not found.
ERROR: Variable DFL not found.
ERROR: Variable RC not found.
ERROR: Variable CC not found.
147 id genotype;
ERROR: Variable GENOTYPE not found.
148 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TOMDIST1 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.TOMDIST1 was not replaced because this step was stopped.
NOTE: PROCEDURE DISTANCE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 355.21k
OS Memory 34980.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 77 Switch Count 0
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
149
150 proc cluster data=tomDist1 method=average outtree=tomTree noprint;
151 id genotype;
ERROR: Variable GENOTYPE not found.
152 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TOMTREE may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.TOMTREE was not replaced because this step was stopped.
NOTE: PROCEDURE CLUSTER used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 362.09k
OS Memory 34980.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 78 Switch Count 0
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
153 axis2 order=(0 to 1.5 by 0.1);
154 proc tree data=tomTree haxis=axis1;
155 id genotype;
ERROR: Variable GENOTYPE not found.
156 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TREE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 408.53k
OS Memory 34980.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 79 Switch Count 0
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
157
158 /** SCREE PLOT **/
159
160 proc factor data=means1 nfactors=4 scree;
161 ods select Eigenvalues ScreePlot;
ERROR: Variable AVR not found.
ERROR: Variable DFL not found.
ERROR: Variable RC not found.
ERROR: Variable CC not found.
162 var avr dfl rc cc ;
163 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FACTOR used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 614.56k
OS Memory 35240.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 80 Switch Count 1
Page Faults 0
Page Reclaims 50
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
WARNING: Output 'ScreePlot' 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.
WARNING: Output 'Eigenvalues' 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.
164
165 /** PRINCIPAL COMPONENET ANALYSIS **/
166
167 proc prinqual data=means1 out=data3 n=4 replace mdpref;
168 transform identity(avr dfl rc cc );
ERROR: Variable AVR not found.
ERROR: Variable DFL not found.
ERROR: Variable RC not found.
ERROR: Variable CC not found.
169 id genotype ;
ERROR: Variable GENOTYPE not found.
170 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DATA3 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.DATA3 was not replaced because this step was stopped.
NOTE: PROCEDURE PRINQUAL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 352.90k
OS Memory 34980.00k
Timestamp 06/03/2021 06:08:03 PM
Step Count 81 Switch Count 0
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
171
172 /** SCATTER PLOT USING %PLOTIT MACRO **/
173
174 %plotit(data=data3, datatype=mdpref2 2,
175 symlen=3, vechead= , symsize=1.2);
ERROR: Multiple non-listing destinations with multiple styles are open.
Destination Style
OUTPUT
HTML5(WEB) HTMLBlue
RTF(WEB) Rtf
PDF(WEB) Pearl
ERROR: The PLOTIT macro ended abnormally.
176 run;
177
178
179
180 /** EXTRACTING VARIANCE COVARIANCE MATRIX **/
181
182 Data st2;
183 set Stat2;
184 if _type_="SS1" then delete;
185
186 if _name_= "avr" then output;
187 if _name_= "dfl" then output;
188 if _name_= "rc" then output;
189 if _name_= "cc" then output;
190
191
192 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
204 I
Hai Tom,
These data were taken from a augmented randomized block design( treatments (individuals in a F2 population) are not randomized but check varieties( SA and Rosana) are randomized). Now I want to find the block effect based on the performances of the check varieties and adjust the treatment values. Then I wanted to do a principle component analysis, correlation analysis and cluster analysis.
DBMS=EXCEL requires access to Microsoft products.
Just use DBMS=XLSX instead to read an XLSX file.
Hello @Balasooriya
The issue appears to be with the import part of your code. Try the following and let us know the outcome
FILENAME REFFILE 'D:\Acadamic\MS plant breeding\4th semestor\Dr Hettiarachchi\trial2.xlsx';
PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.try1 replace;
GETNAMES=YES;
RUN;
Once this is done, the rest of code should work.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.