Compare varibles across observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Compare varibles across observations

compare

hi

I have the following data set

Nameccodedate of ccode
John001122030020140227
John001122030020140227
John001122030020140227
John99011329MP20140227
John99210429MP20140227
John99600668MP20140227
John99600999MP20140227
John99643017MP20140227
John99643151MP20140227
John541209390020140510
John99011329MP20140510
John99110054MP20140510
John99510249MP20140510
John99600999MP20140510
sam001234571220130501
sam114257845920130501

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
start with 00 keep date(2).

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
Trusted Advisor
Posts: 1,204

Re: Compare varibles across observations

Can you show date field as a part of output?

View solution in original post


All Replies
Super User
Super User
Posts: 7,400

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

Nameccodewhasame date
  different cccode
Preferred
  ouput
output
John001122030020140227 Combined
  with 00
0
John001122030020140227 Combined
  with 00
0
John001122030020140227 Combined
  with 00
0
John99011329MP20140227 Combined
  with 00
0
John99210429MP20140227 Combined
  with 00
0
John99600668MP20140227 Combined
  with 00
0
John99600999MP20140227 Combined
  with 00
0
John99643017MP20140227 Combined
  with 00
0
John99643151MP20140227 Combined
  with 00
0
John541209390020140510 Not
  combined with 00
A
John99011329MP20140510 Not
  combined with 00
A
John99110054MP20140510 Not
  combined with 00
A
John99510249MP20140510 Not
  combined with 00
A
John99600999MP20140510 Not
  combined with 00
A
sam001234571220130501 Combined
  with 00
0
sam114257845920130501 Combined
  with 00
0
sam124512457820140501 Combined
  with 00
0
Frankie001242124420140101 Stand
  Alone not combined with other (XX)
1
Frankie4741242124420140512 Not
  combined with 00
B
Super User
Super User
Posts: 7,400

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.

Nameccodewhaoutput
John0011220300201402270
John0011220300201402270
John0011220300201402270
John99011329MP201402270
John99210429MP201402270
John99600668MP201402270
John99600999MP201402270
John99643017MP201402270
John99643151MP201402270
John5412093900201405100
John99011329MP201405100
John99110054MP201405100
John99510249MP201405100
John99600999MP201405100
sam0012345712201305010
sam1142578459201305010
sam1245124578201405010
Frankie0012421244201401011
Frankie47412421244201405120
Super User
Posts: 9,676

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

Trusted Advisor
Posts: 1,204

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:

nameccodedateoutput
Frankie0012421244.0
Frankie47412421244.0
John0011220300.0
John0011220300.0
John0011220300.0
John99011329MP.0
John99210429MP.0
John99600668MP.0
John99600999MP.0
John99643017MP.0
John99643151MP.0
John5412093900.0
John99011329MP.0
John99110054MP.0
John99510249MP.0
John99600999MP.0
sam0012345712.0
sam1142578459.0
sam1245124578.0
Solution
‎06-23-2014 09:26 AM
Trusted Advisor
Posts: 1,204

Re: Compare varibles across observations

Can you show date field as a part of output?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 284 views
  • 0 likes
  • 4 in conversation