Help using Base SAS procedures

By-group -- correct values

Reply
Contributor
Posts: 68

By-group -- correct values

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

HAVEWANT
ID1ID2Transaction_DateVar1Var2Var1Var2
10020030MAR2012greend_hotgreenhot
10020030MAR2012greenhotgreenhot
30045505JUL2012red coldred cold
30045505JUL2012red_acoldred cold
30045515SEP2012redcoldredcold
30045515SEP2012red cppred cold
10020028JAN2013green_chotgreenhot
10020028JAN2013greenhotgreenhot
30045503JUL2013redcoldredcold
30045505JUL2013red vvvvredcold
30045503JUL2013red_ncoldred cold
30045505JUL2013red coldred cold
Super User
Super User
Posts: 7,401

Re: By-group -- correct values

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.

Contributor
Posts: 68

Re: By-group -- correct values

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

I

Super User
Super User
Posts: 7,401

Re: By-group -- correct values

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;

Contributor
Posts: 68

Re: By-group -- correct values

Ι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

Super User
Super User
Posts: 7,401

Re: By-group -- correct values

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

Occasional Contributor
Posts: 12

Re: By-group -- correct values

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;

Regular Learner
Posts: 1

Re: By-group -- correct values

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.

New User
Posts: 1

Re: By-group -- correct values

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;

Ask a Question
Discussion stats
  • 8 replies
  • 419 views
  • 0 likes
  • 5 in conversation