topic Re: export into excel incl. format-settings in SAS Programming

export into excel incl. format-settings

Konkordanz — Wed, 01 Dec 2021 09:59:31 GMT

Hello forum,

My big dataset (~44.000*16 cells) has numeric columns, which has the following fomat:

proc format ;
  value test .="-" other=[numx.6];
run;

The output within works. Now I want to download it into a excel-file. What is the best way for doing that (incl. format-setting)?

I tried it for example with the button "senden an" (english: "send to"). But this step delets the format. For an proc-report-step or an proc-print-step in combination with an export-command seems impossible...probably because the size of my dataset (test: it works with a filtered dataset). Is it possible, that the only way for me the copy-paste-step is?

Thank you!

Re: export into excel incl. format-settings

andreas_lds — Wed, 01 Dec 2021 10:06:01 GMT

ods excel file="";
proc print data= your_data noobs;
run;
ods excel close;

Should work with rather small datasets, like yours.

Re: export into excel incl. format-settings

Konkordanz — Wed, 01 Dec 2021 10:49:44 GMT

That doesnt work. Sas means: "ERROR: The SAS System stopped processing this step because of insufficient memory."

Re: export into excel incl. format-settings

andreas_lds — Thu, 02 Dec 2021 05:43:37 GMT

@Konkordanz wrote:

That doesnt work. Sas means: "ERROR: The SAS System stopped processing this step because of insufficient memory."

Ok, this is unexpected. Please use

proc options group=memory;
run;

to check how much memory is available in your sas session. Is sas running on your local computer or a server? If it runs on a server, the admins may be able to increase the memory, if that does not cause other trouble. If sas runs on your local computer: how much memory does it have?

Re: export into excel incl. format-settings

Konkordanz — Wed, 08 Dec 2021 09:31:12 GMT

Is sas running on your local computer or a server? If it runs on a server, the admins may be able to increase the memory, if that does not cause other trouble. If sas runs on your local computer: how much memory does it have?

Sas is running on a server. Im not sure, if an admin could increase the memory. The result of your Code...

proc options group=memory; run;

...is:

group=MEMORY
 SORTSIZE=1073741824
                   Specifies the amount of memory that is available to the SORT procedure.
 SUMSIZE=0         Specifies a limit on the amount of memory that is available for data summarization procedures when class 
                   variables are active.
 MAXMEMQUERY=0     Specifies the maximum amount of memory that is allocated for procedures.
 MEMBLKSZ=16777216 Specifies the memory block size for Windows memory-based libraries.
 MEMMAXSZ=2147483648
                   Specifies the maximum amount of memory to allocate for using memory-based libraries.
 LOADMEMSIZE=0     Specifies a suggested amount of memory that is needed for executable programs loaded by SAS.
 MEMSIZE=2147483648
                   Specifies the limit on the amount of virtual memory that can be used during a SAS session.
 REALMEMSIZE=0     Specifies the amount of real memory SAS can expect to allocate.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
NOTE: PROZEDUR OPTIONS used (Total process time):
      real time           0.15 seconds
      cpu time            0.00 seconds

The problem is:

The download-option (proc export, proc report) is no option. So I have to copy-paste the result of my code into excel. That would be okay, cause the format is almost perfect. The only problem is the minus. As I said, I use following format-code:

proc format ;
  value test .="-" other=[numx.6];
run;

When I copy-paste the SAS-Output into excel, the minus is a cell with a length of 40 (1x minus and 39x blank spaces). Within SAS it looks normal. What is the reason for that? I mean...it actually needs only few min to search and replace it within excel. But its one more step I would like to spare. Have you a solution for that? Thank you!

Re: export into excel incl. format-settings

andreas_lds — Thu, 09 Dec 2021 06:35:12 GMT

I ignore your comments regarding excel, i hardly use this time-sink.

You should contact your sas-admins, having no limit on realmemsize together with the error message means that the system has not enough memory or others are using to much of it.