topic Re: Move Data from SAS to specific cells in Excel in ODS and Base Reporting

Move Data from SAS to specific cells in Excel

jhh197 — Thu, 22 Jul 2021 19:42:12 GMT

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

Re: Move Data from SAS to specific cells in Excel

Reeza — Thu, 22 Jul 2021 19:45:23 GMT

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.

Re: Move Data from SAS to specific cells in Excel

jhh197 — Thu, 22 Jul 2021 20:21:43 GMT

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 .

Re: Move Data from SAS to specific cells in Excel

Reeza — Thu, 22 Jul 2021 20:45:19 GMT

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.

Re: Move Data from SAS to specific cells in Excel

jhh197 — Thu, 22 Jul 2021 20:48:45 GMT

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

Re: Move Data from SAS to specific cells in Excel

Rydhm — Thu, 22 Jul 2021 21:00:13 GMT

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;

Re: Move Data from SAS to specific cells in Excel

SASKiwi — Thu, 22 Jul 2021 21:11:04 GMT

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.

Re: Move Data from SAS to specific cells in Excel

Reeza — Thu, 22 Jul 2021 21:16:20 GMT

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;

Re: Move Data from SAS to specific cells in Excel

jhh197 — Thu, 22 Jul 2021 21:16:28 GMT

Hi Reeza,

Is there any other option if SAS is on Citrix

Re: Move Data from SAS to specific cells in Excel

jhh197 — Thu, 22 Jul 2021 21:20:36 GMT

I will try this code Reeza Thank you for help

Re: Move Data from SAS to specific cells in Excel

Rydhm — Thu, 22 Jul 2021 21:30:50 GMT

great approach!

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

Re: Move Data from SAS to specific cells in Excel

Reeza — Thu, 22 Jul 2021 21:35:37 GMT

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

Re: Move Data from SAS to specific cells in Excel

jhh197 — Thu, 22 Jul 2021 23:04:05 GMT

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

Re: Move Data from SAS to specific cells in Excel

Reeza — Thu, 22 Jul 2021 23:44:52 GMT

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.

Re: Move Data from SAS to specific cells in Excel

jhh197 — Fri, 23 Jul 2021 03:39:01 GMT

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

Re: Move Data from SAS to specific cells in Excel

jhh197 — Fri, 23 Jul 2021 06:48:29 GMT

Hi Reeza,

Thank you so much it helped .