Problem with SAS Studio reading data from DATALINES

[dammie_101]

Hello everyone!

I am having issues with SAS Studio reading data into the work library using the DATALINES statement.

The dataset I am using have been copied from an MS-Excel document and pasted into the coding window for SAS Studio.

Below are screenshots of part of my code, the log window and the result window.

I will be sincerely grateful if someone could help figure out what exactly is going on.

Thank you very much!

Dami.

codelog (2)log (1)output window

[rudfaden]

You should make a minimal working example of your problem that people can run. That way it is much easier to help you.

[RW9]

Screenshots of of very little help, for the invalid data, have you used tab as the delimiter between the values?  You may have to set 

infile datalines dlm='09'x;

As for the ods error, it looks like, as you haven't given a path for the file, it defaults to its install location, which you can't write to.  Write to somewhere you do have write access:

ods html body='/somewhere/you/have/write/access/ttest.htm';
   

[dammie_101]

thanks for your responses. @rudfaden and @RW9

my sincere apologies for not including the code earlier.

here it goes:

PROC FORMAT;
VALUE AGEE 		1 =  "YOUNG"
				2 = "OLD";
VALUE STATEE 	0 =  "M0"
				1 = "M1";
				RUN;
DATA RAWCT;
INPUT Age	State	Plate	fcrl5	fcrls	fcrla	fcrl1	fcrl6	fcrlb	fcgrt	meanHKG;
datalines;
1	0	1	28.35	33.5	28.74	24.68	29.22	32.02	27.01	21.41
1	0	2	33.7	32.3	32.45	24.62	35	35	26.93	20.79666667
1	0	3	28.59	31.24	32.13	24.68	33.37	30.89	25.85	19.49333333
1	0	4	33.11	35	32.99	26.3	35	34.38	28.39	21.52666667
1	0	5	.	.	.	.	.	.	.	.
1	1	1	29.06	35	29.82	25.28	31.22	33.65	27.09	20.03333333
1	1	2	32.79	35	31.96	25.71	35	34.54	26.03	20.03
1	1	3	27.07	35	33.09	26.71	35	31.99	27.01	18.99666667
1	1	4	31.79	35	27.06	27.83	34.39	33.24	28.7	20.86666667
1	1	5	29.64	35	30.61	28.01	31.85	32.68	31.01	21.14666667
2	0	1	29.21	30.38	30.35	24.83	32.06	33.29	25.31	20.31333333
2	0	2	32.39	29.53	29.87	23.89	35	35	24.85	19.71666667
2	0	3	26.27	31.48	30.63	24.97	32.8	31	25.85	19.99666667
2	0	4	31.35	35	32.85	25.32	32.46	32.44	28.22	21.15
2	0	5	29.6	35	30.22	25.67	31.7	31.35	27.78	21.19666667
2	1	1	28.11	35	28.99	27.74	29.99	32.89	28.05	20.34
2	1	2	30.64	35	32.6	27.16	35	34.18	26.7	20.01
2	1	3	26.68	35	30.7	27.28	31.48	31.65	27.34	20.06333333
2	1	4	30.53	35	25.21	27.9	32.58	32.09	28.76	21.01333333
2	1	5	28.29	35	29.34	27.73	29.99	31.29	28.89	20.67
;
run;
DATA NORMALISEDCT;
SET RAWCT;
	ARRAY RAWCT(11) Age	State	Plate	fcrl5	fcrls	fcrla	fcrl1	fcrl6	fcrlb	fcgrt	meanHKG;
	ARRAY DCT(11)	Age	State	Plate	fcrl5	fcrls	fcrla	fcrl1	fcrl6	fcrlb	fcgrt	meanHKG;
	DO I = 4 TO 11;
 		DCT(I) = MEANHKG - RAWCT(I);
 		DROP I MEANHKG;
	END;
RUN;
ODS HTML;
TITLE1 "DELTA CT COMPUTATION FOR TARGET GENES";
TITLE2 "DCT =  CT OF HKG -  CT OF TARGET";
PROC PRINT DATA = NORMALISEDCT NOOBS;
FORMAT AGE AGEE. STATE STATEE.;
RUN; 
ODS HTML CLOSE;

There are about 75 variables in my original dataset but i have shortened it for the sake of readability.

Thank you very much for your help.

[rudfaden]

Look like you got some hidden characters when you copied from Excel. I typed the first 2 lines of the datalines by hand, and this works.

DATA RAWCT;
INPUT Age State Plate fcrl5 fcrls fcrla fcrl1 fcrl6 fcrlb fcgrt meanHKG;
datalines;
1 1 2 33.7  32.3 32.45 24.62 35 35 26.93 20.79666667
1 0 1 28.35 33.5 28.74 24.68 29.22 32.02 27.01 21.41
;
run;

I would suggest the you import the excel sheet instead of copying data into sas.

[RW9 · Accepted solution]

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.

[dammie_101]

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 

[dammie_101]

@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

[BrianGaines]

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.  

[dammie_101]

---

@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

[BrianGaines]

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?

[dammie_101]

---

@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!

[Tom]

Where did you get the program?

It looks like your program is not using normal spaces in the places that look like spaces on the screen.

Notice how the INPUT statement echoed in the log appears to reference just one variable name?
And how the notes about invalid data appear to be reading the entire line into a single variable?

it might help to copy the code lines into Notepad or other text editor and fix those space issues.

[dammie_101]

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.