topic Re: How to convert a matrix data into a column vector (data set) in SAS Programming

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

AG_Stats — Wed, 24 Feb 2016 18:33:05 GMT


*(Please see two attachments first for data and my objective);

options nocenter nonumber;
libname abc '/folders/myfolders';

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

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

*var abc.import = colvec(abc.import);

PROC print DATA=abc.IMPORT noobs;
RUN;

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

Ksharp — Thu, 25 Feb 2016 02:03:57 GMT

You want IML code ?

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

PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=IMPORT replace;
	GETNAMES=NO; 
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;

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

AG_Stats — Thu, 25 Feb 2016 07:02:09 GMT

Thanks Ksharp,

I get what I need. However, I have started working on SAS just two weeks before, I don't know what is IML code. However, I have searched on google to understand it.

Dose IML code will do something better. Would it improve my programming or does it make my coding short?

Thanks,
Ankit Gulati

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

Ksharp — Thu, 25 Feb 2016 07:09:45 GMT

IML is another sas language , unlike classic data step. The unit it operate is a matrix . Which could get you very fast . But are hard to handle big data due to the need of loading all data into memory.

IML stands for Interactive Matrix Language.

For learning it , Check Rick's blog :

http://blogs.sas.com/content/iml/

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

AG_Stats — Thu, 25 Feb 2016 16:41:52 GMT

Thanks Ksharp.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

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

AG_Stats — Sat, 12 Mar 2016 19:10:29 GMT

Hi Ksharp,

For my last matrix to column vector question I found I more very easy solution. However I appreciate your 'proc sql' logic.

data vect2;
	infile "/folders/myfolders/Matrix/Matrix1.txt" dsd dlm= '	';
		do count = 1 to 2000; /* Giving a big value (Even Bigger than total No. of observations) */
		input X @; drop count; /* Or: input X $ @; */
		output;
	end;
run;

ods csv file = '/folders/myfolders/Matrix/Mat_to_Vec2.csv';
proc print noobs;
run;
ods csv close;

It's very simple.

Thanks

Ankit

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

AG_Stats — Sat, 12 Mar 2016 20:20:19 GMT

Hi Kshrap,

Regarding the same reply could you modify the above code so that it could read .xlsx files directly instead of .txt or .csv (otherwise I have to copy and paste data to a .txt or .csv file. I want to avoid this step). I have tried the following infile statements but only fist (and .csv) working. I want .xlsx extention.

Also, please give me 'ods xlsx' or similar statement to get print / output in an excel (.xlsx) file. (However, I know that I can use right click and choose export option and export 'work.vect2' to an excel file. But I want a code/statement for this.)

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

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

data vect2;
	infile "/folders/myfolders/Matrix/Matrix1.txt" dsd dlm= '	';
	*infile reffile dsd dlm= '	';
	*infile REFFILE;
	*infile "/folders/myfolders/Matrix/Matrix1.xlsx" termstr=cr dsd dlm= '	';
	do count = 1 to 5000; /* Giving a big value (Even Bigger than total No. of observations) */
		input X @; drop count; /* Or: input X $ @; */
		output;
	end;
run;

Thanks,

Ankit

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

AG_Stats — Sat, 12 Mar 2016 20:23:13 GMT

Hi Kshrap,

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

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

data vect2;
	infile "/folders/myfolders/Matrix/Matrix1.txt" dsd dlm= '	';
	*infile reffile dsd dlm= '	';
	*infile REFFILE;
	*infile "/folders/myfolders/Matrix/Matrix1.xlsx" termstr=cr dsd dlm= '	';
	do count = 1 to 5000; /* Giving a big value (Even Bigger than total No. of observations) */
		input X @; drop count; /* Or: input X $ @; */
		output;
	end;
run;

Thanks,

Ankit

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

Ksharp — Sun, 13 Mar 2016 02:14:32 GMT

"so that it could read .xlsx files directly instead of .txt or .csv (otherwise I have to copy and paste data to a .txt or .csv file. I want to avoid this step). I have tried the following infile statements but only fist (and .csv) working. I want .xlsx extention."

I don't understand what you mean. That is what I just did. If you want output be a EXCEL file , just add one more proc export at end of my code.

FILENAME REFFILE "/folders/myfolders/Matrix.xlsx" ;

PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=IMPORT replace;
	GETNAMES=NO; 
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 export data=want outfile="/folders/myfolders/want.xlsx" dbms=xlsx replace;
putnames=no;
run;

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

AG_Stats — Sun, 13 Mar 2016 05:47:43 GMT

Hi Kshrap,

Sorry I have not mentioned my Objective this time.

Now I have a matrix as:

1 4 7

2 5 8

3 6 9

And I want otuput as:

Also I want to take input from .xlsx file and output to .xlsx file; by using the following codes:

data vect2;
 infile "/folders/myfolders/Matrix.xlsx" dsd; /* something is wrong here. */
 
 do count = 1 to 50; 
 input X @; drop count;
 output;
 end;
run;

/* and output in excel file */

(And sorry, this coding is not a short-cut of your proc sql. It was my misunderstanding.)

Thanks,

Ankit

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

AG_Stats — Sun, 13 Mar 2016 05:47:43 GMT

Hi Kshrap,

Sorry I have not mentioned my Objective this time.

Now I have a matrix as:

1 4 7

2 5 8

3 6 9

And I want otuput as:

Also I want to take input from .xlsx file and output to .xlsx file; by using the following codes:

data vect2;
 infile "/folders/myfolders/Matrix.xlsx" dsd; /* something is wrong here. */
 
 do count = 1 to 50; 
 input X @; drop count;
 output;
 end;
run;

/* and output in excel file */

(And sorry, this coding is not a short-cut of your proc sql. It was my misunderstanding.)

Thanks,

Ankit

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

Ksharp — Sun, 13 Mar 2016 09:35:37 GMT

OK.This could be simple as :

FILENAME REFFILE "/folders/myfolders/Matrix.xlsx" ;

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

data IMPORT ;
 set IMPORT ;
 _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;

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

AG_Stats — Sun, 13 Mar 2016 10:43:44 GMT

Thanks Kshrap,

(I get how to use proc export to export the sas data set. But I still do not know how to import .xlsx file in this particular program.)

Could you modify the following program, so that instead of importing raw data from .csv file I can import it from .xlsx file.


/*
FILENAME REFFILE "/folders/myfolders/Matrix.xlsx" ;

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

data vect2;
*set import; 
	infile "/folders/myfolders/Matrix.csv" dsd;  
	do count = 1 to 50;  
	input X @; *drop count; 
	output; 
	end;
run;

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

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

Ksharp — Mon, 14 Mar 2016 00:48:20 GMT

I don't understand what you mean.

If you really want use data step , you can transform the dataset into CSV file ,then import it by data step.

FILENAME REFFILE "/folders/myfolders/Matrix.xlsx" ;

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

proc export data=IMPORT outfile="/folders/myfolders/Matrix.csv" dbms=csv replace;
putnames=no;
run;

data vect2;
	infile "/folders/myfolders/Matrix.csv" dsd;  
	input X @@; 
run;

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

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

AG_Stats — Mon, 14 Mar 2016 04:46:44 GMT

Okay, Kshrap, I get it.

But still there are questions in my mind.

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)?

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

Thanks

Ankit

/* Step 1 */
FILENAME REFFILE "/folders/myfolders/Matrix.xlsx" ;

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

/* Step 2 */
proc export data=IMPORT outfile="/folders/myfolders/Matrix.csv" dbms=csv replace;
putnames=no;
run;

/* Step 3 */
data vect2;
	infile "/folders/myfolders/Matrix.csv" dsd;  
	input X @@; 
run;

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

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

Ksharp — Mon, 14 Mar 2016 06:59:36 GMT

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" ;

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

AG_Stats — Mon, 14 Mar 2016 07:58:29 GMT

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;

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

Ksharp — Mon, 14 Mar 2016 08:13:13 GMT

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.

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

AG_Stats — Mon, 14 Mar 2016 11:19:27 GMT

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

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

AG_Stats — Wed, 13 Apr 2016 13:21:22 GMT

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;

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

NOTE: Libref X was successfully assigned as follows:

Engine: XLSX

Physical Name: /folders/myfolders/Matrix.xlsx

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