SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Aggregating multiple variables by group with a Do loop

Reply
Contributor
Posts: 34

Aggregating multiple variables by group with a Do loop

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:

CIDFirst VisitReturn VisitOrderRevenue
ABC1000
ABC0100
ABC0100
ABC0100
ABC01150

 

And I want:

CIDFirst VisitReturn VisitOrderRevenue
ABC14150

 

Any help is greatly appreciated. 

 

Michael

PROC Star
Posts: 1,167

Re: Aggregating multiple variables by group with a Do loop

Posted in reply to SmcGarrett

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

Contributor
Posts: 34

Re: Aggregating multiple variables by group with a Do loop

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. 

PROC Star
Posts: 1,167

Re: Aggregating multiple variables by group with a Do loop

Posted in reply to SmcGarrett

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;

Super User
Posts: 19,815

Re: Aggregating multiple variables by group with a Do loop

Posted in reply to SmcGarrett

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. 

 

Super User
Posts: 19,815

Re: Aggregating multiple variables by group with a Do loop

Posted in reply to SmcGarrett

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

Ask a Question
Discussion stats
  • 5 replies
  • 749 views
  • 1 like
  • 3 in conversation