topic Re: By-group -- correct values in SAS Procedures

By-group -- correct values

Nikos — Thu, 04 Dec 2014 15:01:54 GMT

Hi.

I am grouping by ID1 and ID2 but Var1 and Var2 contain defect data.

I would like to correct as shown on the WANT

(Var1 and Var2 values may be ubder different ID1 and ID2)

Thanking you in advance

Nikos

HAVE					WANT
ID1	ID2	Transaction_Date	Var1	Var2	Var1	Var2
100	200	30MAR2012	green	d_hot	green	hot
100	200	30MAR2012	green	hot	green	hot
300	455	05JUL2012	red	cold	red	cold
300	455	05JUL2012	red_a	cold	red	cold
300	455	15SEP2012	red	cold	red	cold
300	455	15SEP2012	red	cpp	red	cold
100	200	28JAN2013	green_c	hot	green	hot
100	200	28JAN2013	green	hot	green	hot
300	455	03JUL2013	red	cold	red	cold
300	455	05JUL2013	red	vvvv	red	cold
300	455	03JUL2013	red_n	cold	red	cold
300	455	05JUL2013	red	cold	red	cold

Re: By-group -- correct values

RW9 — Thu, 04 Dec 2014 15:09:02 GMT

Hi,

Could you clarify exactly, with code/test data what you are trying to do. It looks like the data in var_1 actually has more than one distinct, i.e. "green" is not the same as "green_c" so a grouping would still bring these out, however if you are after distincts on ID1/2 then do a pros sort nodupkey. Question then is what do you want to get back. You could also standardize your var1/2 data before grouping.

Re: By-group -- correct values

Nikos — Thu, 04 Dec 2014 15:15:35 GMT

Hi,

I am trying to "correct" Var1 and Var2

The "correct" pairs are (red, cold) (green hot) that should replace the "defect" ones

I am trying to solve that with proc sql find the Min and Max for each group by the proc sort the distinct cases and get the "dupout" dataset so I can remerge it back to the original and "correct" the values but the code becomes two long

I wonder whether there is a shortcut to that.

Thank you

Re: By-group -- correct values

RW9 — Thu, 04 Dec 2014 15:20:29 GMT

This should convert var1/2 to only have the first part of the string, the distinct will then remove any duplicates.

proc sql;

create table WANT as

select distinct

ID1,

ID2,

scan(VAR1,1,"_") as VAR1,

scan(VAR2,1,"_") as VAR2

from HAVE;

quit;

Re: By-group -- correct values

Nikos — Thu, 04 Dec 2014 15:46:07 GMT

Ιt seems that SCAN function does not work since "defect" values may include various kinds of "defection" i.e. vvvv where it should have been cold

Thank you

Re: By-group -- correct values

RW9 — Thu, 04 Dec 2014 16:57:35 GMT

So you are going to need to do some data cleaning prior to the step then. There's no magic "clean my data" button.

Re: By-group -- correct values

nathan_owens — Thu, 04 Dec 2014 17:33:37 GMT

Are the distinct values you expect to see only green and red, hot or cold? if so:

if index(var1,"green")>0 then do;

var1 = 'green'; var2 = 'hot';

end;

else if index(var1,"red")>0then do;

var1 = 'red; var2 = 'cold';

end;

...

proc sort data=... nodupkey; by id1 id2; run;

Re: By-group -- correct values

ChemoDb — Thu, 04 Dec 2014 17:58:21 GMT

For clean up, how about:

1) use proc sql to create a list of distinct values for Var1

2) use data step to detect and translate defective values, write the corresponding desired value to a new var

3) merge the translation table with the original table (by the original var1 values)

The result of merge should give you the original vars and plus your new alternative side by side. repeat for var2.

Re: By-group -- correct values

zenjamie — Thu, 04 Dec 2014 18:13:55 GMT

While there is no substitute for proper cleaning of defective data, a shortcut approach would be to take the most common combination of values to be the "correct" set and apply those to your data. There is no guarantee that this will be correct but the "errors" should be spread across the wide variety of ways it is possible to be wrong so the valid data is anticipated to be the most populous.

In your particular instance it mihgt be more appropriate to hande id1 and var1 separately from id2 and var2 but this might be food for thought:

proc sql noprint;

/*First create distinct combinations of id1, id2, var1 and var2*/

create table

work.groups

as select distinct

id1, id2, var1, var2, count(*) as counter

from

work.have

group by

1,2,3,4

;

/*Then select the most popular combination (on the assumption that this is the one most likely to be correct)*/

create table

work.best_group

as select

id1, id2, var1, var2

from

work.groups

group by

1,2

having

counter=max(counter)

;

/*Finally join the original dataset back to the "best" grouping*/

create table

work.want

as select

h.id1, h.id2, h.Transaction_Date,

bg.var1, bg.var2

from

work.have as h,

work.best_group as bg

where

h.id1 = bg.id1

and

h.id2 = bg.id2

;

quit;