Thank you so very much! I am deeply grateful. ... View more

I am open to learning more. I absolutely would not mind additional explanation. @Kurt_Bremser wrote: @dammie_101 wrote: This works perfectly! I am grateful. Thank you so much! Fine! Is the code clear to you, or do you need additional explanation?   ... View more

understood. I am sincerely grateful for taking time to explain to that depth of detail. ... View more

i think i get the logic:   SAS scans and calls in all variables from the input dataset into array procedure;   set column 'rowproduct' to an initial of 1;   initiate array procedure from column 1 until last element in the array dimension;   rowproduct => (a) call initial value of rowproduct...multiply this with (b) call cell values in array dimension, multiply values) until last entry;             ... View more

This works perfectly! I am grateful. Thank you so much! ... View more

how about in a situation where the column variables have different names? As an example instead of grp1, grp2, grp3 etc the variable names are liverKO kidneyKO bladderKO heartKO lungsKO. (I am going to get to that point in the next couple of weeks in the course of my experiments, hence why i am asking).   what would the line statement   rowproduct = geomean(of grp:)**5 look like?   Thanks in advance. ... View more

@BrianGaines wrote: My suggested fix pertained to being able to copy/paste data from an Excel file into the SAS Studio editor to be used with DATALINES.  However, in the code you provided, you are using PROC IMPORT to import the Excel file directly.  So my suggestion will not help with that.     After selecting the "Substitute spaces for tabs" option, did you try to use the data step to to import the data? Yes, you are right. I did not follow your suggestion to the letter. I apologise.   And yes! It worked! I just did as you suggested and used the data step, and it worked absolutely fine. Thank you very much! ... View more

Thank you for your reply. I prepared the raw data from Microsoft Excel 2013 version and wrote the code.   I noticed and initially I could not figure out why but in the end, i thought i got that error probably because I did not state that the delimiter was TAB in the case of my dataset or SAS Studio just has really big issues with importing TAB delimited .xlsx documents.   And given what other contributors have observed and suggested, I think it is safe to say that SAS Studio does not have the capacity to handle imports from .xlsx-based documents. ... View more

@RW9 wrote: I would agree with @rudfaden, the data imports fine for me as well.  I wouldn't however import an Excel file.  SAve the file to CSV, then use a datastep to read the csv in, specifying informats and lengths and such like.  Proc import + Excel = garbage data. Thank you so much @RW9!!! That worked flawlessly!   Saved the original file in CSV format and imported the dataset into SAS Studio it worked!.   The following from the LOG window indicates that SAS did the job of specifying informats for the dataset. 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 /* Generated Code (IMPORT) */ 74 /* Source File: test_002.csv */ 75 /* Source Path: /folders/myfolders */ 76 /* Code generated on: 14/08/2018 22:23 */ 77 78 %web_drop_table(WORK.rawct); NOTE: Table WORK.RAWCT has been dropped. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 79 80 81 FILENAME REFFILE '/folders/myfolders/test_002.csv'; 82 83 PROC IMPORT DATAFILE=REFFILE 84 DBMS=CSV 85 OUT=WORK.rawct; 86 GETNAMES=YES; 87 RUN; NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST. 88 /********************************************************************** 89 * PRODUCT: SAS 90 * VERSION: 9.4 91 * CREATOR: External File Interface 92 * DATE: 14AUG18 93 * DESC: Generated SAS Datastep Code 94 * TEMPLATE SOURCE: (None Specified.) 95 ***********************************************************************/ 96 data WORK.RAWCT ; 97 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 98 infile REFFILE delimiter = ',' MISSOVER DSD firstobs=2 ; 99 informat Age best32. ; 100 informat State best32. ; 101 informat Plate best32. ; ... ; 248 format meanHKG best12. ; 249 input 250 Age 251 ... 324 meanHKG 325 ; 326 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 327 run; NOTE: The infile REFFILE is: Filename=/folders/myfolders/test_002.csv, Owner Name=root,Group Name=vboxsf, Access Permission=-rwxrwx---, Last Modified=14 August 2018 22:23:02, File Size (bytes)=8010 NOTE: 20 records were read from the infile REFFILE. The minimum record length was 149. The maximum record length was 419. NOTE: The data set WORK.RAWCT has 20 observations and 75 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 20 rows created in WORK.RAWCT from REFFILE. NOTE: WORK.RAWCT data set was successfully created. NOTE: The data set WORK.RAWCT has 20 observations and 75 variables. NOTE: PROCEDURE IMPORT used (Total process time): real time 1.82 seconds cpu time 0.35 seconds 328 329 PROC CONTENTS DATA=WORK.rawct; RUN; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.31 seconds cpu time 0.31 seconds 330 331 332 %web_open_table(WORK.rawct); 333 334 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 347 ... View more

@BrianGaines wrote: As others have noted, when copy/pasting from Excel to the DATA step, the data are separated by special tab characters instead of spaces, which are causing the problem.     Another remedy to the issue is to change your SAS Studio preferences to substitute tabs with spaces.  Depending on which version you are using (this screenshot is from version 3.71), go to Preferences --> Editor --> Check the "Substitute spaces for tabs" option:     Then, if you copy/paste the data back into the editor, the tabs will now display as spaces and the data successfully imported when I tried it.  If you still have issues, then please post back.   I have done as you have explained in the attached screenshot. However, this is what i get when in the LOG window: 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 /* Generated Code (IMPORT) */ 74 /* Source File: test.xlsx */ 75 /* Source Path: /folders/myfolders */ 76 /* Code generated on: 14/08/2018 22:10 */ 77 78 %web_drop_table(WORK.rawct); 79 80 81 FILENAME REFFILE '/folders/myfolders/test.xlsx'; 82 83 PROC IMPORT DATAFILE=REFFILE 84 DBMS=XLSX 85 OUT=WORK.rawct; 86 GETNAMES=YES; 87 RUN; NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. NOTE: The import data set has 20 observations and 75 variables. NOTE: WORK.RAWCT data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.29 seconds cpu time 0.20 seconds 88 89 PROC CONTENTS DATA=WORK.rawct; RUN; NOTE: PROCEDURE CONTENTS used (Total process time): real time 1.47 seconds cpu time 1.45 seconds 90 91 92 %web_open_table(WORK.rawct); 93 94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;   and then when i run the following test code.... proc sort data = work.rawct out = sorted; by age state plate; run; proc print data = sorted; run; proc means data = sorted mean stddev stderr; var fcrl5 fcrls fcgr4; run; i get the following in the LOG window: 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 proc means data = sorted mean stddev stderr; 74 var fcrl5 fcrls fcgr4; ERROR: Variable fcrl5 in list does not match type prescribed for this list. ERROR: Variable fcrls in list does not match type prescribed for this list. ERROR: Variable fcgr4 in list does not match type prescribed for this list. 75 run; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE MEANS used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 76 77 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 90 ... View more

Apparently, you are absolutely correct. IMPORT step did the job of reading in the dataset as @rudfaden suggested, but i got the following in the LOG window:   81 FILENAME REFFILE '/folders/myfolders/test.xlsx'; 82 83 PROC IMPORT DATAFILE=REFFILE 84 DBMS=XLSX 85 OUT=WORK.rawct; 86 GETNAMES=YES; 87 RUN;   NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. NOTE: The import data set has 20 observations and 75 variables. NOTE: WORK.RAWCT data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.07 seconds cpu time 0.05 seconds     And in the end...it meant I could not run as simple as a PROC MEANS step on the variables in the dataset  ... View more