Move Data from SAS to specific cells in Excel

[jhh197]

Hi ,

I have a SAS Dataset  summary_details   which looks like below and I want that dataset to export to specific cells in Excel (c17 to d17 : c20 to d20)

with a specific sheet name (Test60) how do it do that ?

SAS Dataset

Summary_details

_NAME_    COL1

IP_SUM     10

CV_SUM    20

RS_SUM    30

I want above SAS Dataset to be in C17 to d17 and c20 to d20  in excel  and sheet name should be Test 60 . How do I make above data to fall into specific cells in excel . Can anyone please help ?

IP_SUM     10

CV_SUM    20

RS_SUM    30

[Reeza]

Is this going to an already existing Excel file? A brand new file? Are header rows allowed?
If it's a new Excel file and header rows are allowed this is trivial. If it's an existing file, and header rows are allowed it's doable.

[jhh197]

Hi Reeza ,

Excel file is not existing already . It is a new excel file and header rows meaning _NAME_ and Col1 not allowed . But if its not possible I can create one empty excel file within the SAS folder .

[Reeza]

I'd probably make my first row my variable labels in this case and export using ODS EXCEL. Possible, yes. Works, yes. Great solution, No.

[jhh197]

Then How to I move data to specific cells in existing Excel . Can you please help

[Rydhm]

You will need to use DDE approach for this. I don't have a perfect solution because you need to create an empty excel file(test_excel) at any location first with sheet1 renamed to Test60. Then you can use following code. There is sleep function to give time to Excel for loading.. after 5 seconds or so you will see the data in your excel file in given cells.

options noxwait noxsync;
x '"H:\test_excel.xlsx"';

/* Sleep for 5 seconds to give */
/* Excel time to start up.   */

data _null_;
   x=sleep(5);
run;

data one;
input _NAME_ $ COL1;
datalines;
IP_SUM 10
CV_SUM 20
RS_SUM 30
;
run;

/* The DDE link is established using   */

filename data dde 'excel|Test60!r17c3:r20c4'; /*Your cell range*/
data two;
 set one;
   file data;
   put _NAME_ COL1;
run;

[SASKiwi]

DDE will only work if SAS runs on your PC. If you are running on a remote SAS server then you can't use this method.

[Reeza]

DDE is a massive security risk and Excel locked it down in early 2020. You can unlock it though - you may want to run that by your security team.

ods excel file ='/home/fkhurshed/demo.xlsx' options(starts_at="17,3", sheet_name = "test60");
proc print data=have (firstobs=2);
label _name_ = "IP_SUM";
label COL1 = "10";
run;
ods excel close;

---

@Rydhm wrote:

You will need to use DDE approach for this. I don't have a perfect solution because you need to create an empty excel file(test_excel) at any location first with sheet1 renamed to Test60. Then you can use following code. There is sleep function to give time to Excel for loading.. after 5 seconds or so you will see the data in your excel file in given cells.

options noxwait noxsync;
x '"H:\test_excel.xlsx"';

/* Sleep for 5 seconds to give */
/* Excel time to start up.   */

data _null_;
   x=sleep(5);
run;

data one;
input _NAME_ $ COL1;
datalines;
IP_SUM 10
CV_SUM 20
RS_SUM 30
;
run;

/* The DDE link is established using   */

filename data dde 'excel|Test60!r17c3:r20c4'; /*Your cell range*/
data two;
 set one;
   file data;
   put _NAME_ COL1;
run;

---

[jhh197]

Hi Reeza,

Is there any other option if SAS is on Citrix

[jhh197]

I will try this code Reeza Thank you for help

[Rydhm]

great approach!

Just why not use proc report with noheader instead of proc print?

[Reeza]

Because I didn't know that was an option 😄
I concur, ODS EXCEL + PROC REPORT with NOHEADER is a better option than the label.

[jhh197]

Hi Reeza ,
Thank you for all your help . I have tried above code . But I am getting the results in Q3 because we gave starts_at 17 in code . But how do I get results at C15 . Can you please help .



Thank you so much

[Reeza]

Have you tried changing the values in STARTS_AT so that it's C15? That would be Row 15, Col 3. Q3 is col 15, row 3. I'm sure you can figure it out from there.

[jhh197 · Accepted solution]

Hi Reeza ,

Thank you so much . It worked 🙂 . But I get output like below how do  I change code so that I don't get obs , _NAME_ and Col1 and just get the records to excel . Can you please help .

Obs	_NAME_	COL1
1	IP_SUM	10
2	CV_SUM	20
3	RS_SUM	30