DATA Step, Macro, Functions and more

Rollup data - conditional grouping

Reply
Contributor
Posts: 65

Rollup data - conditional grouping

[ Edited ]

I am trying to derive a new column (Column B) based on an existing column (Column A) on my data.

 

 

ColumnA CountofA 

12345         15         

12346         20         

12347         6           

12348         20         

12349        4  

12350        20

21100        6

21111         6

21112       4

21299      2

21399      2

 

 

Desired Output  

ColumnA ColumnB

12345        12345       

12346        12346-12347 (This will get merged since the first four letter are the same) 

12347        12346-12347 (This will get merged since the first four letter are the same)  

12348        12348-12349 (This will get merged since the first four letter are the same) 

12349        12348-12349 (This will get merged since the first four letter are the same)    

12350       12350 

21101         21101-21111 (This will get merged since the first 3 digits are the same and their sum total is 12) 

21111         21101-21111 (This will get merged since the first 3 digits are the same and their sum total is 12) 

21112        21112-21399 (This will get merged since the first 2 digits are the same and their sum tota is 8)

21299        21112-21399 (This will get merged since the first 2 digits are the same and their sum tota is 8)

21399        21112-21399 (This will get merged since the first 2 digits are the same and their sum tota is 8)

 

 

The code that I currently involves lot of manual check and so I thought of posting my question over here to get guidance on solving above scenarios. 

 

Thanks in advance

Super User
Posts: 5,260

Re: Rollup data - conditional grouping

Even with your notes, I have no clue of what the business rule is.

Perhaps you could describe in text your real life scenario, that might help.

Data never sleeps
Super User
Posts: 9,691

Re: Rollup data - conditional grouping

I don't know what you are talking about .

This could give you a start.

 

 

data have;
input ColumnA $  CountofA ;
cards;
12345         15         
12346         20         
12347         6           
12348         20         
12349        4  
12350        20
21100        6
21111         6
21112       4
21299      2
21399      2
;
run;
proc sql;
create table want as
 select a.*,b.ColumnA as b_ColumnA ,abs(compare(a.ColumnA ,b.ColumnA )) as distance
  from have as a,have as b
   where a.ColumnA ne b.ColumnA 
    group by a.ColumnA
     having calculated distance=max(calculated distance)
      order by a.ColumnA,b.ColumnA  ;
quit;
Contributor
Posts: 65

Re: Rollup data - conditional grouping

Sorry for the poor description. 

Here's what I am trying to achieve: 

Basically, I have a table which contains unique 5 digit number and total record counts associated with it. Now the 5 digit numbers are not in sequence, however they could have either the first four digits or first three or first two digits in  common. (01521, 01529). Now using this numbers, I want to roll up the date based on each number. So let's say if number 01521 have more than 10 records, however 01529 doesn't have 10 records, I would like to create a new column which will tell me which numbers where grouped together (for ex: 01521-01529). Here's are some sample records: 

Column A  # of Records

01111             111 

01119              6 

Now since the first four digits are common for above numbers, I would like to create a new column with value for each row as '01111-01119'. 

Similarly, here is the second scenario where I have exhaused the first four combinations. So I would go for matching the first three digits : 

Column A # of Records

01255          99

01299           6

 

Again I only need to do a grouping if the number of records are less than 10. 

Here is the code that I have which definitely not the best piece of code: 

 

data want;
merge test1
test1 (firstobs=2 rename=(columna=_columna1 count=_count1 flag=_flag1 ))
test1 (firstobs=3 rename=(columna=_columna2 count=_count2 flag=_flag2 ))
;


if count (# of records) < 10 then do;

 

if substr(columna,1,4) = substr(_columna1,1,4) then do;


if count + _count1 >= 10 then derived_column = strip(column) || '-' || strip(_columna1);

 

else if substr(column,1,4) = substr(_columna2,1,4) then do;
if count + _count1 + _count2 >= 10 then do;
derived_column = strip(column) || '-' || strip(_columna2);
end;


end;

 

The problem with above program is I am not able to derive the new column for records that have value of > 10.

 

 

HTH. Thanks!

 

Ask a Question
Discussion stats
  • 3 replies
  • 297 views
  • 0 likes
  • 3 in conversation