How to convert a matrix data into a column vector (data set)

[Ksharp]

Q1. Could we save our steps in this program. Can we avoid step 2. Can we directly read .xlsx file in step 3 (as we are reading .csv file here)?

No. You can't avoid Step2 unless you use my last code(operate it in SAS dataset). 

No.You can't  directly read .xlsx file in step 3 unless you want to use DDE skill ,but that is too old skill .

Q2. Could we combine Step 1 and Step 2 in one step (or could we do anything new)?

No. I don't konw how to combine them , Use my last code or Use LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

[AG_Stats]

Thanks Kshrap,

But, I could not understand how to use: LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

Could you rectify the following codes:

(I am getting the following error: ERROR: No logical assign for filename RD.)

LIBNAME rd XLSX "/folders/myfolders/Matrix.xlsx";

data vect2;
	  
	infile rd;
	input X @@; 
run;

proc export data=vect2 outfile="/folders/myfolders/want6.xlsx" dbms=xlsx replace;
putnames=no;
run;

[Ksharp]

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

is the same as 

PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=IMPORT replace;
GETNAMES=NO;
RUN;

So you can operate the table like this. But I don't think that is what you want.

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

data IMPORT ;
set x.'Sheet1$'n;
_n+1;
run;
proc transpose data=IMPORT out=want(where=(_name_ ne '_n'));
by _n;
var _all_;
run;

proc export data=want(keep=col1) outfile="/folders/myfolders/want.xlsx" dbms=xlsx replace;
putnames=no;
run;

According to your requirment, Check DDE skill, but I don't familiar with it . Ask other people.

[AG_Stats]

Thanks Kshrap,

I have learned many things from you. But still I have a doubt in last code and I will discuss it after 20th March.

Thanks 

Ankit

[AG_Stats]

Hi Kshrap,

I have tried to run your last codes. But unfortunately I am getting 2 errors:

I have runned this:

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

data IMPORT ;
set x.'Sheet1$'n;
_n+1;
run;

Then see the log(s):

/*

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

55

56

57 LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

NOTE: Libref X was successfully assigned as follows:

Engine: XLSX

Physical Name: /folders/myfolders/Matrix.xlsx

58

59 data IMPORT ;

60 set x.'Sheet1$'n;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File X.'Sheet1$'n.DATA does not exist.

61 _n+1;

62 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.IMPORT may be incomplete. When this step was stopped there were 0 observations and 1 variables.

NOTE: DATA statement used (Total process time):

real time 0.07 seconds

cpu time 0.01 seconds

 */

Note: I ensure you that the required excel file is at right location and has required sheet viz. sheet1. Also, I am using SAS Studio v3.4 or 3.5.

*****************************************************************************************************************************************************************

Besides this, if I want to read Sheet1 and Sheet2 of a (Excel) file for different purpose then is there any shortcut to save steps. Till I used to do it as:

For example, for simplicity, my aim is to print the output dataset (sheet1 and sheet2):

FILENAME ago "/folders/myfolders/Learning/Matrix.xlsx" TERMSTR=CR;

PROC IMPORT DATAFILE=ago
DBMS=XLSX
OUT=WORK.IMPORT10;
GETNAMES=No;
SHEET="Sheet1";
RUN;

proc print data= import10 noobs; run;

FILENAME ago "/folders/myfolders/Learning/Matrix.xlsx" TERMSTR=CR;

PROC IMPORT DATAFILE=ago
DBMS=XLSX
OUT=WORK.IMPORT11;
GETNAMES=yes;
SHEET="Sheet2";
RUN;

proc print data= import11 noobs; run;

Note: Here, I am using GETNAMES option = no for sheet1 and then yes for sheet2.

1. However, for simplicity you can assume it as yes for both cases.

2. Also, consider the same problem (give me shortcut steps) if this option is no for sheet1 and yes for sheet2.

Also note that if I do it as: 

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

proc print data= x.'sheet2$'n;
run;

Then I am getting the same error for print procedure. (Libname statement is fine).

(I am waiting for your reply)

Thanks

Ankit

[AG_Stats]

Hi Kshrap,

I have tried to run your last codes. But unfortunately I am getting 2 errors:

I have runned this:

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

data IMPORT ;
set x.'Sheet1$'n;
_n+1;
run;

Then see the log(s):

/*

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

55

56

57 LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

NOTE: Libref X was successfully assigned as follows:

Engine: XLSX

Physical Name: /folders/myfolders/Matrix.xlsx

58

59 data IMPORT ;

60 set x.'Sheet1$'n;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File X.'Sheet1$'n.DATA does not exist.

61 _n+1;

62 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.IMPORT may be incomplete. When this step was stopped there were 0 observations and 1 variables.

NOTE: DATA statement used (Total process time):

real time 0.07 seconds

cpu time 0.01 seconds

 */

Note: I ensure you that the required excel file is at right location and has required sheet viz. sheet1. Also, I am using SAS Studio v3.4 or 3.5.

*****************************************************************************************************************************************************************

Besides this, if I want to read Sheet1 and Sheet2 of a (Excel) file for different purpose then is there any shortcut to save steps. Till I used to do it as:

For example, for simplicity, my aim is to print the output dataset (sheet1 and sheet2):

FILENAME ago "/folders/myfolders/Learning/Matrix.xlsx" TERMSTR=CR;

PROC IMPORT DATAFILE=ago
DBMS=XLSX
OUT=WORK.IMPORT10;
GETNAMES=No;
SHEET="Sheet1";
RUN;

proc print data= import10 noobs; run;

FILENAME ago "/folders/myfolders/Learning/Matrix.xlsx" TERMSTR=CR;

PROC IMPORT DATAFILE=ago
DBMS=XLSX
OUT=WORK.IMPORT11;
GETNAMES=yes;
SHEET="Sheet2";
RUN;

proc print data= import11 noobs; run;

Note: Here, I am using GETNAMES option = no for sheet1 and then yes for sheet2.

1. However, for simplicity you can assume it as yes for both cases.

2. Also, consider the same problem (give me shortcut steps) if this option is no for sheet1 and yes for sheet2.

Also note that if I do it as: 

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

proc print data= x.'sheet2$'n;
run;

Then I am getting the same error for print procedure. (Libname statement is fine).

(I am waiting for your reply)

Thanks

Ankit Gulati

[Ksharp]

Maybe your Sheet Name is not right.

Can you use PROC IMPORT instead of LIBNAME ?

[AG_Stats]

Hi Kshrap,

My Sheet Name was absosutely right.

Thanks I understand that no need to use filename staement while importing next sheet.

Proc Import is working well but can I save some more steps? Or can I do something else for saving steps?

Why the following set statement is not working in my SAS while it is working in yours.

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

data IMPORT ;
set x.'Sheet1$'n;
_n+1;
run;

I have also tried it for some other raw data(s).

Thanks

Ankit

[AG_Stats]

Hi Kshrap,

Now fortunately I get idea from SQL query and the following codes are now working:

libname newlib xlsx '/folders/myfolders/learning/matrix.xlsx';

data IMPORT ;
set newlib.Sheet1;
_n+1;
run;

But now I have some different problem. Consider the following codes:

proc print data= newlib.sheet1 noobs;
run;

proc print data= newlib.sheet2 noobs;
run;

Both are now running but, in first sheet I have data from first row (some numeric observations), and proc print is taking first row as variables names. How can I define that there are no variable names (in Sheet1)  like in porc import we uses - getname option = No.

Thanks 

Ankit

[Ksharp]

Sorry. I don't know how to get it , if you are using EXCEL engine ,you can use NOHEADER.

But for XLSX engine ,it doesn't work. Maybe you could check the documentation to find a such kind of option.

libname newlib xlsx '/folders/myfolders/learning/matrix.xlsx' noheader;

[Ksharp]

FILENAME REFFILE "/folders/myfolders/Matrix.xlsx" TERMSTR=CR;

PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=IMPORT replace;
	GETNAMES=NO; 
	sheet='Sheet1';
RUN;

data _null_;
 set sashelp.vcolumn(where=(libname='WORK' and memname='IMPORT')) end=last;
if _n_ eq 1 then call execute('proc sql;create table want as');
if type='num' then call execute(cat('select put(',name,',best.) as ',name,'from IMPORT'));
 else call execute(cat('select ',name,' from IMPORT'));
if not last then call execute('union all');
 else call execute(';quit;');
run;

proc print;run;



PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=IMPORT replace;
	GETNAMES=NO; 
	sheet='Sheet2';
RUN;

data _null_;
 set sashelp.vcolumn(where=(libname='WORK' and memname='IMPORT')) end=last;
if _n_ eq 1 then call execute('proc sql;create table want as');
if type='num' then call execute(cat('select put(',name,',best.) as ',name,'from IMPORT'));
 else call execute(cat('select ',name,' from IMPORT'));
if not last then call execute('union all');
 else call execute(';quit;');
run;

proc print;run;