How to export the data to the Excel range

[art297]

A first step would be to find out where you can actually create a file. Can you use proc export to create an Excel file of sashelp.class? If so, what specifically did you use as the path and name of the file?

[SASdevAnneMarie]

Yes, I did that, my log is:

1                                                          The SAS System                           14:43 Friday, September 25, 2020

1          ;*';*";*/;quit;run;

2          OPTIONS PAGENO=MIN;

3          %LET _CLIENTTASKLABEL='Programme';

4          %LET _CLIENTPROCESSFLOWNAME='Flux de processus';

5          %LET _CLIENTPROJECTPATH='';

6          %LET _CLIENTPROJECTPATHHOST='';

7          %LET _CLIENTPROJECTNAME='';

8          %LET _SASPROGRAMFILE='';

9          %LET _SASPROGRAMFILEHOST='';

10        

11         ODS _ALL_ CLOSE;

12         OPTIONS DEV=PNG;

13         GOPTIONS XPIXELS=0 YPIXELS=0;

14        

15         GOPTIONS ACCESSIBLE;

16         Proc Export Data=sashelp.class

17         Outfile= "\\laxxxx.com\LMPAPROD\USINE_PRIIPS\5. RETRAITEMENTS MANUELS\Chaine Profils\4.

17       ! Analyse\Marie_TEST\Class_test.xlsx"

18         dbms=xlsx

19         replace;

20        

21         quit;

NOTE: The export data set has 19 observations and 5 variables.

NOTE: "\\ laxxxx.com\LMPAPROD\USINE_PRS\5. RETRAITEMENTS MANUELS\Chaine Profils\4. Analyse\Marie_TEST\Class_test.xlsx" file

      was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.30 seconds

      cpu time            0.03 seconds

22        

23         GOPTIONS NOACCESSIBLE;

24         %LET _CLIENTTASKLABEL=;

25         %LET _CLIENTPROCESSFLOWNAME=;

26         %LET _CLIENTPROJECTPATH=;

27         %LET _CLIENTPROJECTPATHHOST=;

28         %LET _CLIENTPROJECTNAME=;

29         %LET _SASPROGRAMFILE=;

30         %LET _SASPROGRAMFILEHOST=;

31        

32         ;*';*";*/;quit;run;

33         ODS _ALL_ CLOSE;

34        

35        

36         QUIT; RUN;

37        

[art297]

You can use range= with proc export if you use dbms=xlsx. However, as I recall, that will allow you to write to a range but not an existing range. I.e., you can't modify an existing worksheet that way.

I did notice a possible difference between your running proc export and running the macro. With proc export you enclosed the outfile with double quotes. You don't appear to have done that with the macro. Definitely worth a try .. i.e., enclosing the name supplied to the outfile parameter with double quotes.

You could always try a dde approach but that, of course, comes with all of the problems that has caused everyone to stop using dde.

[SASdevAnneMarie]

Thank you, Art,

I'm trying this code, but I have an error: 

data toto;
input X1 X2;
cards; 
10 10
;
run;

Proc Export Data=toto
	Outfile= "\\laxxx.com\LMPAPROD\USINE_PRIIPS\5. RETRAITEMENTS MANUELS\Chaine Profils\4. Analyse\Marie_TEST\Class_test.xlsx"
	dbms=xlsx
		replace;
	sheet=Class_test;
	Range='Class_test$D2:E2';
quit;

Proc Export Data=toto

18              Outfile= "\\laxxx.com\LMPAPROD\USINE_PRIIPS\5. RETRAITEMENTS MANUELS\Chaine Profils\4.

18       ! Analyse\Marie_TEST\Class_test.xlsx"

19              dbms=xlsx

20                    replace;

NOTE: The previous statement has been deleted.

21              sheet=Class_test;

22              Range='Class_test$D2:E2';

            _____

            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

23         quit;

[art297]

Try with the same code, but

1) remove the sheet option and

2) add a semicolon, after the sheet name, in the range option

[SASdevAnneMarie]

it doesn't work:

23 Proc Export Data=toto
24 Outfile= "\\laxxx.com\LMPAPROD\USINE_S\5. RETRAITEMENTS MANUELS\Chaine Profils\4.
24 ! Analyse\Marie_TEST\Class_test.xlsx"
25 dbms=xlsx
26 replace;
NOTE: The previous statement has been deleted.
27 Range='Class_test;$D2:E2';
_____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

28 quit;

[art297]

I was apparently wrong about that dbms=xlsx can use a range option. I was also wrong about the semi-colon, as I was actually thinking exclamation point, but that wouldn't have helped. Other than getting the macro to work, or DDE or writing your own vb code, I can only think that you might be able to use ODS and proc report. However, I can't be of much/any help there.

[SASKiwi]

@SASdevAnneMarie  - You can specify a range if you use DBMS = EXCEL in your PROC IMPORT. Check out this doc link .

[SASdevAnneMarie]

Thank you for your messge, SASKiwi.
Unfortunately, I'm looking for EXPORT.

[SASKiwi]

Sorry, my bad...but if you check the doc link it should work for PROC EXPORT too.