*(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;
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;
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;
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 :
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
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
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
"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;
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
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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.