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" ;
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;
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.
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
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):
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):
Maybe your Sheet Name is not right.
Can you use PROC IMPORT instead of LIBNAME ?
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
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
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;
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;
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.