Aggregating multiple variables by group with a Do loop

[SmcGarrett]

Hello,

I have a data set with a customer ID and multiple variables for different transactions. 

Each transaction is a new observation so the customer ID is duplicated. The transactions are within a macro variable "&R".

I believe I need to execute a "do-loop" within proc sql but I am having trouble. 

I have:

CID	First Visit	Return Visit	Order	Revenue
ABC	1	0	0	0
ABC	0	1	0	0
ABC	0	1	0	0
ABC	0	1	0	0
ABC	0	1	1	50

And I want:

CID	First Visit	Return Visit	Order	Revenue
ABC	1	4	1	50

Any help is greatly appreciated. 

Michael

[TomKari]

This would be quite straightforward if your observations were in a SAS dataset. Do they need to be in a macro variable? What form do they take in the variable?

Tom

[SmcGarrett]

The data is read into a SAS dataset. I just used excel for this purpose. 

Yes, they do need to be in a macro variable. 

The data is re-run weekly so the transactions will change if there are no orders or revenue or others... (there are over 15 different transactions")

The data is originally read in a text format and I use a scan function to read the values that exist in the "Transacation Type" field and then use a do loop to create columns for each transaction. So if I specify the Transaction Types without doing a scan, I run the risk of losing transactions that are different from one week to the next. 

They are all numeric. 

[TomKari]

Since you have your data in a macro variable, I can't think of any other way than DATA STEP programming to do your summarization.

If your variables were in a SAS dataset, I believe the following would meet your needs.

Tom

proc sql noprint;

create table Want as

select CID, sum(FirstVisit) as SumFirstVisit, sum(ReturnVisit) as SumReturnVisit, sum(Order) as SumOrder, sum(Revenue) as SumRevenue

from Have

group by CID;

quit;

[Reeza]

I don't agree that you need your macro variables. 

Explain your process further, it should be possible to accommodate your issues within a data step or proc SQL. Maybe include a case where you feel macros have to be involved. 

It looks like a proc summary would work but I'm probably missing something. 

[Reeza]

Also, are you posting in Data Management because your using DI Studio or because of the subject of your question.