topic How to check whether observation 1=2 etc. in one variable in SAS Programming

How to check whether observation 1=2 etc. in one variable

Xamius32 — Tue, 17 Jul 2012 13:58:37 GMT

I need to know how to check and output data where observation 1=observation 2=observation 3 for variable X by variable Y (IE variable Y has customer IDs, so I want to check whether the codes in variable X equal each other. Some customers have from 2 to 5 observations.)

so for example:

cust ID code

1 5

1 6

1 10

2 3

SO i would want cust ID 1 outputted to one particular data set and cust ID 2 outputted to a different data set.

Is this something I would need an array for?

Re: How to check whether observation 1=2 etc. in one variable

Haikuo — Tue, 17 Jul 2012 14:10:07 GMT

Hi,

Are you trying to subset your data set by 'ID's?

Haikuo

Re: How to check whether observation 1=2 etc. in one variable

Xamius32 — Tue, 17 Jul 2012 14:12:16 GMT

I want all the data in which the codes are equal to be subsetting into a dataset and all the codes that dont match to go into a different dataset. I hope that makes sense. I do not want each ID to have its own dataset, but I am examining the codes by ID.

Re: How to check whether observation 1=2 etc. in one variable

Tom — Tue, 17 Jul 2012 14:17:23 GMT

Easy to do with PROC SQL code.

proc sql noprint;

create table one as select * from have where count(distinct code) = 1 group by custid;

create table two as select * from have where count(distinct code) > 1 group by custid;

quit;

If you want to do it with a data step here is a way do it using a couple of do loops to check if the given id value has multiple code values and then output the records to right database. If you only want the id values output then you can eliminate the second do loop.

data one two ;

do until (last.custid);

set have;

by custid ;

if not first.custid and _previous ne code then different=1;

_previous=code;

end;

do until (last.custid);

set have ;

by custid;

if different=1 then output two;

else output one;

end;

run;

Re: How to check whether observation 1=2 etc. in one variable

Xamius32 — Tue, 17 Jul 2012 14:28:56 GMT

Hmm I do get what you are saying for the SQL step,

proc sql noprint;

create table team3.testing1 as select * from team3.multi_reward

where count(distinct reward_r) = 1 group by cust_id;

/*create table two as select * from have where count(distinct code) > 1 group by custid;*/

quit;

When I put that in, it says

ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

What am I doing wrong?

Re: How to check whether observation 1=2 etc. in one variable

Tom — Tue, 17 Jul 2012 14:34:58 GMT

That's what I get for posting untested code.

create table team3.testing1 as

select *

from team3.multi_reward

group by cust_id

having count(distinct reward_r) = 1

;

Re: How to check whether observation 1=2 etc. in one variable

Xamius32 — Tue, 17 Jul 2012 14:50:45 GMT

Haha I feel dumb now Thanks for the help.

Re: How to check whether observation 1=2 etc. in one variable

Howles — Fri, 20 Jul 2012 17:46:29 GMT

data have ;

input custID code ; cards ;

1 5

1 6

1 10

2 3

;

proc sql ;

create view all as

select range(code) as not_same , *

from have

group by custID

order by custID , code ;

quit ;

data same not_same ;

set all ;

if not_same then output not_same ;

else output same ;

drop not_same ;

run ;

Re: How to check whether observation 1=2 etc. in one variable

Haikuo — Sat, 21 Jul 2012 18:27:28 GMT

The more, the merrier: (original idea was first seen from Mike zdeb's post, I think)

data have ;

input custID code ; cards ;

1 5

1 6

1 10

2 3

;

data same notsame;

set have (in=up) have;

by custid;

retain _t;

if up and first.custid then do; call missing(_t,_f);_t=code;end;

if code ne _t then _f+1;

if not up then do;

if _f ge 1 then output notsame;

else output same;

end;

drop _:;

run;

Haikuo