BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AG_Stats
Quartz | Level 8

*(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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

25 REPLIES 25
Ksharp
Super User

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;
AG_Stats
Quartz | Level 8
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
Ksharp
Super User

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/

AG_Stats
Quartz | Level 8
Thanks Ksharp.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Regards,
AG_Stats
AG_Stats
Quartz | Level 8

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
AG_Stats
Quartz | Level 8

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
AG_Stats
Quartz | Level 8

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
Ksharp
Super User

"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;
AG_Stats
Quartz | Level 8

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
AG_Stats
Quartz | Level 8

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
Ksharp
Super User

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;
AG_Stats
Quartz | Level 8

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
Ksharp
Super User

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;
AG_Stats
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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