proc import(for numerous variables)

[okyk_86]

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.

[Reeza]

DBMS=XLSX

[okyk_86]

ok. I revised it ^^;; 

[Kurt_Bremser]

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?

[okyk_86]

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

[Kurt_Bremser · Accepted solution]

---

@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.

[okyk_86]

OK thank you for your help ^^