topic Re: macros problem in ODS and Base Reporting

macros problem

deleted_user — Wed, 18 Jul 2007 05:28:01 GMT

Exercise:
There are 12 Data Files (one for each Month) which contain data for Orders (Order Id, Product Id, Sales).There is a separate Data File for Product Referential which contains the Product Name, Feature, SKU Number.

Write a SAS Program which reads these files and creates 12 SAS Datasets. There is probablity of Duplicate data and hence duplicate data based on orderid should be eliminated.

Write a Macro which will take as input 2 parameters (FromMonth and ToMonth). It should merge the data from the FromMonth to ToMonth and then should summarise the results based on Quarters.

Write another Macro which will merge the above created dataset with the Product Referntial and generate a ODS HtML Report for the summarised data for each product details and quater.

Re: macros problem

deleted_user — Thu, 19 Jul 2007 01:58:28 GMT

This looks very much like a homework problem, and you aren't likely to have people doing your homework for you.

Kind regards

David

Re: macros problem

Cynthia_sas — Thu, 19 Jul 2007 03:24:26 GMT

Hi,
David is right. This does look like a homework problem. However, as a teacher, I am willing to offer the following suggestions:

1) have a working SAS program that reads one or more of the 12 monthly files using hard-coded input and output file names;

1a) After the program is working for 1 or 2 files, modify this program to read all 12 files (perhaps consider using a macro %do loop or the FILEVAR/FILENAME combination to read the 12 files)

2) have a working SAS program that reads the Product Info file using hard-coded input and output file names;

3) have a working SAS program that merges that output file from #1 with the Product file from #2 (decide whether you will use a merge or an sql join to get your resulting file)

4) At some point, in this process, use a where clause with hardcoded from and to dates to make sure that you can select the correct date range from either the file you create in #1 or as you do the merge or the join in #3. (I would probably include the where selection in the #3 program.

5) Now, go back and start to "macro-ize" the programs. Use %let statements for setting values like the from and to dates. Depending on how you write the program for #1, you might consider using numbered macro variables for each monthly file.

6) After you have the "macro-ized" program working with hard-coded %LET statements, create a macro program or "set" of macro programs. Perhaps you would turn program #1 into a single macro program called %readdata -- you will probably set macro parameters that will take the place of the %LET statements. It is in this macro program that you will add any macro condition logic, like %IF or add %DO loops.

7) then you might combine programs #2 and #3 into another macro program called %seldata -- again you will probably set your macro parameters for from and to dates here.

8) Test your macro programs. This options statement will help you debug any errors you get:
[pre]
options mprint mlogic symbolgen;
[/pre]

Finally, continue refining your program until the final output is the output you want.
Designing a macro program of this complexity is an iterative process. No matter what you do, you MUST have working SAS programs to start with. .

cynthia

corrected %let in #5...thanks, david!
Message was edited by: Cynthia@sas

Re: macros problem

deleted_user — Thu, 19 Jul 2007 08:35:59 GMT

Well put Cynthia, now Vasu is still left with actually doing the work and learning how to do it.

I will clarify though that in your point number 5, I'm sure you meant to write "%Let", not "&Let" for the macro symbol assignments. Since you used %Let in number 6, it was clearly a littoral that you would have picked up when you ran the code as you gradually built up the solution from tested and working SAS code. (That's a great message that is worthy of repetition, and might almost make it on the 10 rules of good SAS programming.)

I hope the homework reflects the support provided.

Incidentally, Chris H commented on my posting the 1000th message on the forums a while back, but I see that on your own your postings are currently more than half that figure. That's a very big contribution, and I see well deserved comments reflecting appreciation for your work.

Kind regards

David