Help using Base SAS procedures

proc import(for numerous variables)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

proc import(for numerous variables)

[ Edited ]

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
Solution
‎03-13-2017 06:03 AM
Super User
Posts: 7,832

Re: proc import(for numerous variables)


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 19,845

Re: proc import(for numerous variables)

DBMS=XLSX

Occasional Contributor
Posts: 6

Re: proc import(for numerous variables)

ok. I revised it ^^;; 

Super User
Posts: 7,832

Re: proc import(for numerous variables)

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: proc import(for numerous variables)

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
Solution
‎03-13-2017 06:03 AM
Super User
Posts: 7,832

Re: proc import(for numerous variables)


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: proc import(for numerous variables)

Posted in reply to KurtBremser

OK thank you for your help ^^ 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 327 views
  • 2 likes
  • 3 in conversation