BookmarkSubscribeRSS Feed
bob021
Calcite | Level 5
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
11 REPLIES 11
DBailey
Lapis Lazuli | Level 10
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.
ballardw
Super User
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.
bob021
Calcite | Level 5
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.
Ksharp
Super User
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
bob021
Calcite | Level 5
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.
Ksharp
Super User
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
bob021
Calcite | Level 5
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
Ksharp
Super User
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.
bob021
Calcite | Level 5
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
Ksharp
Super User
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
bob021
Calcite | Level 5
Hi Ksharp
That is perfect. Thank you very much for your answers.
Bob

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1252 views
  • 0 likes
  • 4 in conversation