DATA Step, Macro, Functions and more

How to export values into excel?

Posts: 21

How to export values into excel?

Hey guys! Smiley Happy


I've got a task, which is quite hard to do it. It's basically macro programming.

The task is about that I have to import a file from excel by a macro (class.xlsx). This is not something that I can't solve. Smiley Very Happy The hardest part is coming now.


"The output file (excel as well, exporting, not hard tho. Smiley Happy ) should contain columns. One column should be the field name, and the other is the values of it. (For example, from the sashelp.class dataset, there will be a coloumn with the fieldname 'Sex', and it will be shown twice in the output file. For once with 'M' and for the other with 'F'.)"


Honestly guys, I don't even understand correctly the task, not even to make it. :/ I made an exporting and an importing macro tho, but nothing more. Can you help me? Smiley Happy


Thank you for your answers! Smiley Happy

Have a good day!

Posts: 9,367

Re: How to export values into excel?

[ Edited ]

It is not clear to me what you need. CLASS.XLSX is the name of an Excel file, not the name of a SAS macro or an Excel macro. So I don't know where "macro" comes into your requirement.

When you are using SAS, the possibility exists that you may need to IMPORT data from some other format (SPSS, Excel, Oracle, CSV) into SAS format; or you may need to EXPORT data from SAS format to some other data format (SPSS, Excel, Oracle, etc). There are many different ways to do this without using an Excel macro or a SAS macro. The Programming 1 class teaches the use of the SAS Libname engine for Excel, which is one way to do an EXPORT or an IMPORT.

However, SAS has some very handy procedures, very nicely named PROC IMPORT and PROC EXPORT, which will work for what you need too.

For example, to take SASHELP.CLASS, a SAS dataset with 19 observations and send it to an Excel worksheet in a workbook, you can use PROC EXPORT. However, PROC EXPORT can only sent over to Excel what is in the file it starts with. So, since SASHELP.CLASS has 19 observations, the resulting Excel worksheet would have 19 rows of data and one header row.

In your post, you seemed to imply that you needed to see only a column for sex and to have it "shown twice in the output file". Well, that is odd because SASHELP.CLASS has 5 columns: NAME, SEX, AGE, HEIGHT, and WEIGHT.

Do you perhaps need the COUNT of females and the COUNT of males in the original data in Excel? Your requirements are not entirely clear.

You said you don't understand the task and so, that is going to make it difficult for anyone to help you if you don't understand it, then you can't explain it. Perhaps you should go back to the person or book that gave you the task and ask for clarification or read the requirements of the task again. Where did you get hte idea that any kind of macro was necessary? Was that somewhere in your instructions?

Perhaps you need something like this:
Sex Frequency Percent
F      9        47.37
M     10        52.63

That shows the COUNT and PERCENT of females and Males? But that is not something you can directly produce using PROC EXPORT.

The Programming 1 class also shows the use of PROC FREQ which is the procedure you can use to generate frequency counts and percents, as well as cumulative counts and cumulative percents. There are 2 ways to create Excel output from PROC FREQ:
1) create an output dataset from PROC FREQ with the summary information and then use PROC EXPORT from the output dataset
2) use ODS Excel or ODS CSV with the PROC FREQ to generate the procedure results directly in Excel format (depending on whether you want an output with colors and font (ODS EXCEL) or plain output (ODS CSV).

We show the use of ODS and PROC FREQ in the Programming 1 class. Programming 1 is a free class as self-paced e-learning.

Hope this helps,

Super User
Super User
Posts: 9,599

Re: How to export values into excel?

I can help in one very important place.  Macro is not where you want to be working.  Macro is an advanced topic which is only present as a Base SAS code generator (a bit like a find and replace).  You should learn Base SAS thoroughly as if you cannot do it in that, you will struggle to do it in macro which is not a replacement.


Now your problem breaks down into:
1) Import data from file - you can use various methods, simplest (although prone to rubbish as Excel is a bad format and this procedure is a guessing proc) is proc import - well described in the manual.

2) Process the data to get what you want.  From your description proc freq is probably the simplest method - again well defined in the manual.

3) Export to excel - for a basic data dump proc export, for a file with more formatting maybe tagsets.excelxp.  

Very simple.


Note on future posts, to get good answers, follow some simple guidanec which is found below where you posted:

- Post test data in the form of a datastep

- Post example output required

- Explain any logic to be used on the data.

Posts: 21

Re: How to export values into excel?

Thank you all, for answers. Smiley Happy I figured it out now, and I just made it more complicated than it is, so basically it wasn't that hard as I tought. Anyways, thanks for advice and help. Smiley Happy

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation