- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi.
I'm trying to import xlsx.files but all of the variables doesn't being imported.
My variables are about 1,500 .
I have several excel files that have to import so want to use macro.
How can I import this file using macro?
I used the following code:
proc import out = work.data1
datafile = 'C:\sw\desktop\data1.xlsx'
dbms=xlsx replace;
sheet=sheet1;
getnames=yes;
run;
my data:
date APPLE AT&T CB ... MS ...
01.01 2000 1.2 1.3 1.2 ... 4.2 ...
02.01 2000 1.3 1.5 1.7 ... 3.2 ...
Your help is truly appreciated.
Thanks.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@okyk_86 wrote:
If I could change my variable names( permno1 to permno1,500), is there any way to use macro?
Or how about use variable orders(ex: var1 var2 var3...var1500)? T_T
Absolutely. Variable names like that lend themselves to all kinds of easier code.
You can then even use them in lists:
format var1-var1500 10.;
So it depends on the attributes of the columns. If they're all numeric, the data step can be extremely simple to write, and you won't even have problems with overlong statements (a single SAS statement must not exceed 32767 characters in length).
If you have groups of variables with different attributes, using macro preprocessing will be the method of choice.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
DBMS=XLSX
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
ok. I revised it ^^;;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Save to csv and write a data step manually to import from that. If you have patterns of variables, use the macro processor to help in creating repeating code.
Since you have an incredibly high number of columns, you might run into limitations of the maximum SAS statement length, so split your variable definition (length, format etc) and input statements.
Who comes up with the idea that 1500 columns can be practicable?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Or how about use variable orders(ex: var1 var2 var3...var1500)? T_T
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@okyk_86 wrote:
If I could change my variable names( permno1 to permno1,500), is there any way to use macro?
Or how about use variable orders(ex: var1 var2 var3...var1500)? T_T
Absolutely. Variable names like that lend themselves to all kinds of easier code.
You can then even use them in lists:
format var1-var1500 10.;
So it depends on the attributes of the columns. If they're all numeric, the data step can be extremely simple to write, and you won't even have problems with overlong statements (a single SAS statement must not exceed 32767 characters in length).
If you have groups of variables with different attributes, using macro preprocessing will be the method of choice.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OK thank you for your help ^^