BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,
I need to import a password protected excel file into sas. I've checked in the forum and i've found someone who suggested to use FILENAME and DDE engine, becouse DDE acceps parameter like a password, but i don't know how to do it.

X '"C:\Documents and Settings\A.xls"';
filename datafile dde 'Excel|C:\Documents and Settings\[A.xls]sheet1!r2c1:r6c3';
data tmp;
infile datafile missover;
input A B C;
run;


Can anybody help me?
I've looked a solution everywhere but i haven't found anything, so if it's not possible to import a protected excel file to sas, please, let me know and at least i will try another solution.

Thanks!
Elena
2 REPLIES 2
Oleg_L
Obsidian | Level 7
Hello!

To read password protected Excel file using DDE:

1. start Excel.

2. open file using open command (where abcdxxx is a password 😞

filename cc dde 'Excel|System';
data _null_;
file cc; put '[Open("c:\documents\wg.xls",0,1,,"abcdxxx")]';
run;

3. Read data from opened file (where SA_Format is a name of Excel sheet):

filename bs dde "excel|SA_FORMAT!r1c1:r1000c4";
data balconst.sarp;
infile bs ls=5000 missover notab dlm='09'x dsd;
informat totlevel 5. acc $8. accnr $200. accnr2 $200.;
input totlevel acc accnr accnr2 ;
run;

Regards,
Oleg.
deleted_user
Not applicable
Thanks!!!!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 5638 views
  • 0 likes
  • 2 in conversation