Help using Base SAS procedures

Count and merge rows.

Reply
Contributor
Posts: 60

Count and merge rows.

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
Super Contributor
Posts: 578

Re: Count and merge rows.

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.
Super User
Posts: 11,343

Re: Count and merge rows.

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.
Contributor
Posts: 60

Re: Count and merge rows.

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.
Super User
Posts: 10,023

Re: Count and merge rows.

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
Contributor
Posts: 60

Re: Count and merge rows.

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.
Super User
Posts: 10,023

Re: Count and merge rows.

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
Contributor
Posts: 60

Re: Count and merge rows.

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
Super User
Posts: 10,023

Re: Count and merge rows.

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.
Contributor
Posts: 60

Re: Count and merge rows.

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
Super User
Posts: 10,023

Re: Count and merge rows.

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
Contributor
Posts: 60

Re: Count and merge rows.

Hi Ksharp
That is perfect. Thank you very much for your answers.
Bob
Ask a Question
Discussion stats
  • 11 replies
  • 206 views
  • 0 likes
  • 4 in conversation