## Compare varibles across observations

Solved
Occasional Contributor
Posts: 18

# Compare varibles across observations

compare

hi

I have the following data set

 Name ccode date of ccode John 0011220300 20140227 John 0011220300 20140227 John 0011220300 20140227 John 99011329MP 20140227 John 99210429MP 20140227 John 99600668MP 20140227 John 99600999MP 20140227 John 99643017MP 20140227 John 99643151MP 20140227 John 5412093900 20140510 John 99011329MP 20140510 John 99110054MP 20140510 John 99510249MP 20140510 John 99600999MP 20140510 sam 0012345712 20130501 sam 1142578459 20130501

Hi

I want to compare across observations the date of ccode across
ccode having unique conditions.

Basically if ccode starts with  00 keep date(1).

If ccode does not

If date 1 and 2 is
the same new variable created Ccodedate same for both having different ccode.

Accepted Solutions
Solution
‎06-23-2014 09:26 AM
Posts: 1,270

## Re: Compare varibles across observations

Can you show date field as a part of output?

All Replies
Super User
Posts: 9,599

## Re: Compare varibles across observations

Hi,

Could you provide example of what you want as output as I could not follow your logic.  If ccode start with 00, well all 3 of the first do, but they all have the same date anyways.  for sam, the first record has 00, but the second record not ...?

Occasional Contributor
Posts: 18

## Re: Compare varibles across observations

 Name ccode wha same date  different cccode Preferred  ouput output John 0011220300 20140227 Combined  with 00 0 John 0011220300 20140227 Combined  with 00 0 John 0011220300 20140227 Combined  with 00 0 John 99011329MP 20140227 Combined  with 00 0 John 99210429MP 20140227 Combined  with 00 0 John 99600668MP 20140227 Combined  with 00 0 John 99600999MP 20140227 Combined  with 00 0 John 99643017MP 20140227 Combined  with 00 0 John 99643151MP 20140227 Combined  with 00 0 John 5412093900 20140510 Not  combined with 00 A John 99011329MP 20140510 Not  combined with 00 A John 99110054MP 20140510 Not  combined with 00 A John 99510249MP 20140510 Not  combined with 00 A John 99600999MP 20140510 Not  combined with 00 A sam 0012345712 20130501 Combined  with 00 0 sam 1142578459 20130501 Combined  with 00 0 sam 1245124578 20140501 Combined  with 00 0 Frankie 0012421244 20140101 Stand  Alone not combined with other (XX) 1 Frankie 47412421244 20140512 Not  combined with 00 B
Super User
Posts: 9,599

## Re: Compare varibles across observations

Sorry, your example really isn't clear to me, what output do you want, a table which says combined or not for each record, that could be done in the below code.  I don't know what the output column is supposed to represent, e.g. a or b?

data have;
length name ccode \$200 date 8.;
format date date9.;
infile cards;
input name \$ ccode \$ date yymmdd8.;
cards;
John 0011220300 20140227
John 0011220300 20140227
John 0011220300 20140227
John 99011329MP 20140227
John 99210429MP 20140227
John 99600668MP 20140227
John 99600999MP 20140227
John 99643017MP 20140227
John 99643151MP 20140227
John 5412093900 20140510
John 99011329MP 20140510
John 99110054MP 20140510
John 99510249MP 20140510
John 99600999MP 20140510
sam 0012345712 20130501
sam 1142578459 20130501
;
run;

proc sql;
create table WANT as
select  CORE.*,
case  when exists(select distinct THIS.DATE from WORK.HAVE THIS where THIS.DATE=CORE.DATE and substr(THIS.CCODE,1,2)="00") then "Combined with 00"
else "Not combined" end as result
from    WORK.HAVE CORE;
quit;

Occasional Contributor
Posts: 18

## Re: Compare varibles across observations

hi

If you notice John has different Ccodes on the same date.

i am looking for Ccodes staring with 00  that have a only one date. something like Frankie.

if there is more than 1 ccode starting with 00 on the same date that is still fine.

 Name ccode wha output John 0011220300 20140227 0 John 0011220300 20140227 0 John 0011220300 20140227 0 John 99011329MP 20140227 0 John 99210429MP 20140227 0 John 99600668MP 20140227 0 John 99600999MP 20140227 0 John 99643017MP 20140227 0 John 99643151MP 20140227 0 John 5412093900 20140510 0 John 99011329MP 20140510 0 John 99110054MP 20140510 0 John 99510249MP 20140510 0 John 99600999MP 20140510 0 sam 0012345712 20130501 0 sam 1142578459 20130501 0 sam 1245124578 20140501 0 Frankie 0012421244 20140101 1 Frankie 47412421244 20140512 0
Super User
Posts: 10,784

## Re: Compare varibles across observations

If I understood what you mean .

data have;
length name ccode \$ 200 date 8.;
format date date9.;
infile cards expandtabs truncover;
input name \$ ccode \$ date yymmdd10.;
cards;
John     0011220300     20140227
John     0011220300     20140227
John     0011220300     20140227
John     99011329MP     20140227
John     99210429MP     20140227
John     99600668MP     20140227
John     99600999MP     20140227
John     99643017MP     20140227
John     99643151MP     20140227
John     5412093900     20140510
John     99011329MP     20140510
John     99110054MP     20140510
John     99510249MP     20140510
John     99600999MP     20140510
sam     0012345712     20130501
sam     1142578459     20130501
sam     1245124578     20140501
Frankie     0012421244     20140101
Frankie     47412421244     20140512
;
run;
proc sort data=have ;by name date   ;run;
data want;
set have;
by name date;
if first.date and last.date and  ccode eq: '00' then output=1;
else output=0;
run;

Xia Keshan

Posts: 1,270

## Re: Compare varibles across observations

proc sql;
create table a as
select name,ccode,date,count(date) as cnt_date from have
group by name,date;
quit;

proc sql;
create table want (drop=cnt_date) as
select *,case when cnt_date=1 and substr(ccode,1,2)='00' then 1 else 0 end as output from a;
quit;

Occasional Contributor
Posts: 18

## Re: Compare varibles across observations

hi

I am still getting the ouput with ouput as follows:

 name ccode date output Frankie 0012421244 . 0 Frankie 47412421244 . 0 John 0011220300 . 0 John 0011220300 . 0 John 0011220300 . 0 John 99011329MP . 0 John 99210429MP . 0 John 99600668MP . 0 John 99600999MP . 0 John 99643017MP . 0 John 99643151MP . 0 John 5412093900 . 0 John 99011329MP . 0 John 99110054MP . 0 John 99510249MP . 0 John 99600999MP . 0 sam 0012345712 . 0 sam 1142578459 . 0 sam 1245124578 . 0
Solution
‎06-23-2014 09:26 AM
Posts: 1,270

## Re: Compare varibles across observations

Can you show date field as a part of output?

🔒 This topic is solved and locked.