BookmarkSubscribeRSS Feed
dliang
Calcite | Level 5

I have this macro here that import the SAS dataset into excel, but it only works for a non-protected sheet. Please see my code below.

 

 

%let stufilein=U:\Reports and Data\TN Reporting;
%let stufileout=U:\Reports and Data\TN Reporting\TN_DL\Test;
%MACRO OPENxlsx(FOLDER=,IN=);

%LET FIL=;
DATA _null_;
LENGTH FILE $300.;
FILE="'&STUFILEin\&folder.&in.'";
FILE="'"!!TRIM(LEFT(TRANWRD(FILE,"'",'"')))!!"'";
CALL SYMPUT ("FIL",TRIM(LEFT(FILE)));
RUN;

options noxwait noxsync;
x &fil.;
data _null_;
filename commands dde "Excel|system";

/*run;*/
%MEND OPENxlsx;

/*********************************************************************************/
/* TO SAVE ACTIVE xlsx FILE UNDER SPECIFIC FOLDERS AND QUIT EXCEL */
/*********************************************************************************/
%MACRO CLOSExlsx(out=,quit=0);
%LET FIL=;
DATA _null_;
LENGTH FILE $300.;
FILE="[save.as('&STUFILEout.\&out.')]";
FILE="'"!!TRIM(LEFT(TRANWRD(FILE,"'",'"')))!!"'";
CALL SYMPUT ("FIL",TRIM(LEFT(FILE)));
RUN;
%PUT &FIL=;
data _null_;
file commands;
put &fil.;


put '[CLOSE()]';
%IF &quit=0 %then %do;
put '[QUIT()]';
stop;
run;
filename commands clear;
%END;
%IF &quit=1 %then %do;
run;
%END;
%MEND CLOSExlsx;

%OPENxlsx(FOLDER=,IN=ER_IP_TN_XX_YYYYMMDD_NEW_NEW.xlsm); /* Open the Excel template*/
option missing="" ;

/* First dataset is output in a range starting at row 4 column 6 and ending at row 4 column 6*/
filename TAB DDE "EXCEL|[ER_IP_TN_XX_YYYYMMDD_NEW_NEW.xlsm]TN ER & IP 24 Log!R4C1:R50C70";
filename xlcmds DDE "EXCEL|SYSTEM";
data _null_; /* Data step to output the data in the Excel file */
set nonarchive_&&initial&m.;
file TAB notab lrecl=7000;

PUT PATIENT_FIRST_NAME '09'x
PATIENT_LAST_NAME '09'x
DOB '09'x
TDOC_CHAR '09'x

;
run;

filename TAB DDE "EXCEL|[ER_IP_TN_XX_YYYYMMDD_NEW_NEW.xlsm]Archive!R4C1:R200C70";
filename xlcmds DDE "EXCEL|SYSTEM";
data _null_; /* Data step to output the data in the Excel file */
set archive_&&initial&m.;
file TAB notab lrecl=7000;

PUT PATIENT_FIRST_NAME '09'x
PATIENT_LAST_NAME '09'x
DOB '09'x
TDOC_CHAR '09'x
;
run;


%CLOSExlsx(OUT= &&source&m.);


/*%end;*/
%end;
%mend reports;
%reports;

 

I found a code to open a protected sheet with password. But I do not know where I should put it in this code. Can I get some advice?

/*put '[Open("'"&STUFILEin\ER_IP_TN_XX_YYYYMMDD_NEW_test.xlsm"'",,,,"'"centurion"'")]';*/

1 REPLY 1

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 297 views
  • 0 likes
  • 2 in conversation