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
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 |
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 .
Then How to I move data to specific cells in existing Excel . Can you please help
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;
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.
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;
Hi Reeza,
Is there any other option if SAS is on Citrix
great approach!
Just why not use proc report with noheader instead of proc print?
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.