Hi:
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
or
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,
cynthia
