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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

6 REPLIES 6
Reeza
Super User

DBMS=XLSX

okyk_86
Fluorite | Level 6

ok. I revised it ^^;; 

Kurt_Bremser
Super User

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
Fluorite | Level 6
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
Super User

@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
Fluorite | Level 6

OK thank you for your help ^^ 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1495 views
  • 2 likes
  • 3 in conversation