topic Re: Count and merge rows. in SAS Procedures

Count and merge rows.

bob021 — Fri, 29 Apr 2011 15:30:04 GMT

HI All
Can somebody help me with this?
This is my table:

name no code CNT no2 code2
A 20 sc2000 1 50 TT2000
A 30 sc6000 2 80 TT0000
A 60 sc2000 3
B 20 sc2000 1 30 TT2000
B 40 sc2000 2 50 TT0000
C 20 sc2000 1 50 TT2000
C 60 sc2000 2 80 TT0000
C 70 sc3000 3

This is what I need as result.

name no code CNT no2 code2
A 20,30 sc2000,sc6000 1 50 TT2000
A 60 sc2000 3 80 TT0000
B 20 sc2000 1 30 TT2000
B 40 sc2000 2 50 TT0000
C 20 sc2000 1 50 TT2000
C 60,70 sc2000,sc3000 3 80 TT0000

Thank you in advance.
Bob

Re: Count and merge rows.

DBailey — Fri, 29 Apr 2011 16:00:03 GMT

I don't think anyone is going to be able to help without some idea of what logic you're using to get to the second table.

Re: Count and merge rows.

ballardw — Fri, 29 Apr 2011 21:29:13 GMT

Or without knowing how many levels of match/mismatch are needed, either fixed such as first two as shown, running two where the second of your displayed pairs could then be paired with a third, or an indefinate number.

Re: Count and merge rows.

bob021 — Mon, 02 May 2011 08:35:28 GMT

Hi DBailey / ballardw
Thank you very much for answers.
I get this results after left join two tables on name=name and CNT=CNT(first: name,no,code, cnt; second: : name,no2,code2, cnt ). If you take a look at fist two rows in result (A) you will see that no 20 and 30 are merged as well as code sc2000,sc6000. Reason for merging is because ‘code’ 20 and 30 are less than first next ‘code2’.(50) and I want to get ascending order. It could be more than two ‘no’ and ‘code’ for merging.
Is it possible to write such query?
Or to get result like this in left join step:
A 20 sc2000 1
A 30 sc6000 2 50 TT2000
A 60 sc2000 3 80 TT0000
B 20 sc2000 1 30 TT2000
B 40 sc2000 2 50 TT0000
C 20 sc2000 1 50 TT2000
C 60 sc2000 2
C 70 sc3000 3 80 TT0000
In that case I can’t use CNT for left join.

Or it is easier to merge cell after append second tab table to first and order by ‘name’ and ‘no’ (merge all ‘no’ and ‘code’ until code start with TT?) I don’t know is something like that possible.
A 20 sc2000 1
A 30 sc6000 2
A 50 TT2000
A 60 sc2000 3
A 80 TT0000
B 20 sc2000 1
B 30 TT2000
B 40 sc2000 2
B 50 TT0000
C 20 sc2000 1
C 50 TT2000
C 60 sc2000 2
C 70 sc3000 3
C 80 TT0000

Then I can separate table, count again (CNT) and left joint on the end.

If you have any suggestion or solutions please let me know.
Thank you again.

Re: Count and merge rows.

Ksharp — Tue, 03 May 2011 02:45:32 GMT

I still not understand what your logic is totally.
Maybe you need to post some more data and more explain what your logic is.

Ksharp

Re: Count and merge rows.

bob021 — Tue, 03 May 2011 07:17:39 GMT

Hi Ksharp.
Thank you for your respond.
I will try to explain better.
name no no2
A 20 50
A 30 80
A 60
B 20 30
B 40 50
C 20 50
C 60 80
C 70

name no no2
A 20,30 50
A 60 80
B 20 30
B 40 50
C 20 50
C 60,70 80

Every ‘name’ (A,B,C) go thought the process :‘no’ and getting there number (20,30,40,50....) and then go to the next level ‘no2’ where also getting a number (for 10,20,30 larger then at ‘no’). This process can be repeated several times. My problem is when one ‘name’ (in this case A) goes to ‘no’ twice (or more) in row (20,30) and after that to ‘no2’(50).What I want is to merge cell column ‘no’ in case like that. Only way how to determinate this is to count 20, 30 in ‘no’ and stop when find first next in ‘no2’ (50)means that A was twice in a row at ‘no’.
In B process is correct 40 – 50 60 – 70
In C fist is ok but after first ‘no2’ C goes twice thought the ‘no’ and then again to ‘no2’
Thank you in advance.

Re: Count and merge rows.

Ksharp — Tue, 03 May 2011 11:35:20 GMT

OK.
I think understand much more.If you have some other problem ,So I will be here tomorrow,For the sake that I have to leave now.It is not very convenient for to surf Internet. :<

Ksharp

[pre]
data temp;
infile datalines truncover;
input name $ no no2 ;
datalines;
A 20 50
A 30 80
A 60
B 20 30
B 40 50
C 20 50
C 60 80
C 70
;
run;
data op(where=(no_value is not missing));
set temp;
array n{*} no:;
do i=1 to dim(n);
no_name=vname(n{i});
no_value=n{i};
output;
end;
drop i no no2;
run;
proc sort data=op;
by name no_value;
run;
data op;
set op;
by name no_name notsorted;
length no_char $ 50;
retain no_char;
if first.no_name then call missing(no_char);
no_char=catx(',',no_char,no_value);
if no_name eq 'no' then flag+1;
if last.no_name then do;
_name=catx(',',name,flag);
output;
end;
run;
proc transpose data=op(keep=_name no_name no_char) out=want;
by _name;
id no_name;
var no_char;
run;
data want;
set want;
name=scan(_name,1,',');
drop _name _name_;
run;

Message was edited by: Ksharp Message was edited by: Ksharp

Re: Count and merge rows.

bob021 — Wed, 04 May 2011 07:01:29 GMT

Thank you very much Ksharp working like a charm.
One more question about this.
How to adjust query for column which I forgot to mention?

name no date code no2 date2 code2

Thank you again.
Bob

Re: Count and merge rows.

Ksharp — Wed, 04 May 2011 09:12:10 GMT

You need post some data and what do you want output data look like.
For your first post ,I do not understand this:
[pre]
name no code CNT no2 code2
A 20,30 sc2000,sc6000 1 50 TT2000
A 60 sc2000 3 80 TT0000
B 20 sc2000 1 30 TT2000
B 40 sc2000 2 50 TT0000
C 20 sc2000 1 50 TT2000
C 60,70 sc2000,sc3000 3 80 TT0000
[/pre]

Why A is 1 and C is 3.

Re: Count and merge rows.

bob021 — Wed, 04 May 2011 10:20:57 GMT

Hi
It was just a count because a try to do something with that.
These are a real data:
name no date code no2 date2 code2
A 20 1.5.2011 sc2000 50 1.5.2011 TT2000
A 30 1.5.2011 sc6000 80 1.5.2011 TT0000
A 60 1.5.2011 sc2000 1.5.2011
B 20 1.5.2011 sc2000 30 1.5.2011 TT2000
B 40 1.5.2011 sc2000 50 1.5.2011 TT0000
C 20 4.5.2011 sc2000 50 4.5.2011 TT2000
C 60 4.5.2011 sc2000 80 4.5.2011 TT0000
C 70 4.5.2011 sc3000 4.5.2011

Thank you
Bob

Re: Count and merge rows.

Ksharp — Thu, 05 May 2011 03:24:16 GMT

OK. I notice your date date2 has the same value ,So I do not process them.
Look this:
[pre]
data temp;
infile datalines truncover dsd dlm=' ';
input name $ no date : mmddyy10. code $ no2 date2 : mmddyy10. code2 $;
format date date2 mmddyy10.;
datalines;
A 20 1.5.2011 sc2000 50 1.5.2011 TT2000
A 30 1.5.2011 sc6000 80 1.5.2011 TT0000
A 60 1.5.2011 sc2000 1.5.2011
B 20 1.5.2011 sc2000 30 1.5.2011 TT2000
B 40 1.5.2011 sc2000 50 1.5.2011 TT0000
C 20 4.5.2011 sc2000 50 4.5.2011 TT2000
C 60 4.5.2011 sc2000 80 4.5.2011 TT0000
C 70 4.5.2011 sc3000 4.5.2011
;
run;

data op(where=(no_value is not missing));
set temp;
array n{*} no:; array d{*} date:; array c{*} $ code:;
do i=1 to dim(n);
no_name=vname(n{i});date_name=vname(d{i});code_name=vname(c{i});
no_value=n{i};date_value=d{i};code_value=c{i};
output;
end;
drop i ;

run;
proc sort data=op;
by name no_value;
run;
data op;
set op;
by name no_name notsorted;
length no_char code_char $ 50;
retain no_char code_char;
if first.no_name then call missing(no_char,code_char);
no_char=catx(',',no_char,no_value);
code_char=catx(',',code_char,code_value);

if no_name eq 'no' then flag+1;
if last.no_name then do;
_name=catx(',',name,flag);
output;
end;
run;

proc transpose data=op out=want1(keep=_name no no2);
by _name;
id no_name ;
var no_char ;
run;
proc transpose data=op out=want2(keep=_name code code2);
by _name;
id code_name ;
var code_char;
run;
proc transpose data=op out=want3(keep=_name date date2);
by _name;
id date_name;
var date_value;
run;
data want;
merge want1 want2 want3;
by _name;
format date date2 mmddyy10.;
run;

data want;
set want;
length name $ 20;
name=scan(_name,1,',');
drop _name ;
run;

*re-order of variables;
data want;
retain name no date code no2 date2 code2;
set want;
run;
[/pre]

Ksharp

Re: Count and merge rows.

bob021 — Thu, 05 May 2011 06:18:22 GMT

Hi Ksharp
That is perfect. Thank you very much for your answers.
Bob