DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

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

[ Edited ]

*(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;

Regards,
AG_Stats

Accepted Solutions
Solution
‎02-25-2016 01:55 AM
Super User
Posts: 10,041

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

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;

View solution in original post


All Replies
Solution
‎02-25-2016 01:55 AM
Super User
Posts: 10,041

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

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;
Contributor
Posts: 65

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

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
Regards,
AG_Stats
Super User
Posts: 10,041

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

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/

Contributor
Posts: 65

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

Thanks Ksharp.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Regards,
AG_Stats
Contributor
Posts: 65

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

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

Regards,
AG_Stats
Contributor
Posts: 65

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

[ Edited ]

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

 

Regards,
AG_Stats
Contributor
Posts: 65

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

[ Edited ]

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

Regards,
AG_Stats
Super User
Posts: 10,041

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

"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;
Contributor
Posts: 65

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

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: 

1

4

7

2

.

.

9

 

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

Regards,
AG_Stats
Contributor
Posts: 65

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

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: 

1

4

7

2

.

.

9

 

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

Regards,
AG_Stats
Super User
Posts: 10,041

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

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;
Contributor
Posts: 65

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

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;
Regards,
AG_Stats
Super User
Posts: 10,041

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

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;
Contributor
Posts: 65

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

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;
Regards,
AG_Stats
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 25 replies
  • 1092 views
  • 11 likes
  • 2 in conversation