topic Re: proc import(for numerous variables) in SAS Procedures

proc import(for numerous variables)

okyk_86 — Tue, 14 Mar 2017 15:55:39 GMT

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.

Re: proc import(for numerous variables)

Reeza — Mon, 13 Mar 2017 06:46:28 GMT

DBMS=XLSX

Re: proc import(for numerous variables)

okyk_86 — Mon, 13 Mar 2017 07:39:34 GMT

ok. I revised it ^^;;

Re: proc import(for numerous variables)

Kurt_Bremser — Mon, 13 Mar 2017 08:05:28 GMT

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?

Re: proc import(for numerous variables)

okyk_86 — Mon, 13 Mar 2017 09:06:26 GMT

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

Re: proc import(for numerous variables)

Kurt_Bremser — Mon, 13 Mar 2017 09:17:05 GMT

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

Re: proc import(for numerous variables)

okyk_86 — Mon, 13 Mar 2017 10:03:23 GMT

OK thank you for your help ^^