topic Re: Aggregating multiple variables by group with a Do loop in SAS Data Management

Aggregating multiple variables by group with a Do loop

SmcGarrett — Mon, 20 Jun 2016 17:45:14 GMT

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

Re: Aggregating multiple variables by group with a Do loop

TomKari — Mon, 20 Jun 2016 17:58:46 GMT

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

Re: Aggregating multiple variables by group with a Do loop

SmcGarrett — Mon, 20 Jun 2016 18:04:10 GMT

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.

Re: Aggregating multiple variables by group with a Do loop

TomKari — Mon, 20 Jun 2016 19:10:53 GMT

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;

Re: Aggregating multiple variables by group with a Do loop

Reeza — Mon, 20 Jun 2016 19:25:56 GMT

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.

Re: Aggregating multiple variables by group with a Do loop

Reeza — Mon, 20 Jun 2016 19:27:37 GMT

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