Data Manipulation

[Ujjawal]

I have 3 columns of data shown in the table below. It's the same format that exist in my data set. I want minimum 1-R2 value of each cluster against their cluster name.

Cluster	Variable	1-R**2 Ratio
Cluster 1	brclus2	0.4901
	MIPhone	0.005
	MIPOS	0.005
	MIPOSAmt	0.005
	MICC	0.005
	MICCBal	0.005
	MICCPurc	0.005
Cluster 2	HMVal	0.3206
	Age	0.4632
	MIIncome	0.0247
	MIHMOwn	0.0811
	MILORes	0.0247
	MIHMVal	0.0247
	MIAge	0.1094
Cluster 3	Dep	0.4235
	Checks	0.3383
	Teller	0.5586
Cluster 4	MTGBal	0.039

The desired output is shown in the table below :

Cluster	Variable	1-R**2 Ratio
Cluster 1	MIPhone	0.005
Cluster 2	MIIncome	0.0247
Cluster 3	Checks	0.3383
Cluster 4	MTGBal	0.039

Thanks in anticipation!

[LinusH]

So your input data is like a report?

Use retain in the data step to get the cluster name/no on each line.

But how do you chose which "variable" 1-R**2 Ratio-values to keep if there are many that have the same value, the original order?

If so, you could a couple of different techniques. One is to again use retain to keep track on the minimum value. Then read the output database.

Another can be to use SQL. Use monotonic() to keep track of original sorting order, and use having  min(row_no) = row_no and min(1-R**2 Ratio) = 1-R**2 Ratio

[shubhayog]

Hi,

How can we retain the cluster values, can you please explain with SAS Code?

Sorry but I am new so please dont mind.

Regards,

Yogesh

[LinusH]

Go support.sas.com.

Search for RETAIN statement. There you will have syntax and tons of samples.

By reading your response, I think you need some basic SAS programming training.

[data_null__]

RETAIN can't magically fix your data problem as implied by @linus you will need some kind of additional logic.  I like to use UPDATE for this, it can fix a number of variables without having to do too much work.

data cluster;
   input Cluster &$10.  Variable $ Ratio;
   cards;
Cluster 1   brclus2  0.4901
.  MIPhone  0.005
.  MIPOS 0.005
.  MIPOSAmt 0.005
.  MICC  0.005
.  MICCBal  0.005
.  MICCPurc 0.005
Cluster 2   HMVal 0.3206
.  Age   0.4632
.  MIIncome 0.0247
.  MIHMOwn  0.0811
.  MILORes  0.0247
.  MIHMVal  0.0247
.  MIAge 0.1094
Cluster 3   Dep   0.4235
.  Checks   0.3383
.  Teller   0.5586
Cluster 4   MTGBal   0.039
;;;;
   run;
proc print;
   run;
data cv / view=cv;
   retain dummy 1;
   set cluster;
   keep dummy cluster;
   run;
data filled;
   update cv(obs=0) cv;
   by dummy;
   set cluster(drop=cluster);
   output;
   drop dummy;
   run;
proc print;
   run;

[shubhayog]

Hi,

Thank you very much for help.

Regards,

Yogesh

[Jim_G]

Something like this.     This doesn't answer  multiple variables with same low ratio.     Jim

    Data;    retain cluster $8.;

       input @1 clu  $8.   @20 variable $8.   @35 ratio;

           if clu=:'clust'  then cluster=clu;     **** grab cluster;

      datalines;

      proc sort;  by cluster ratio;

      data new;  set;  by cluster;

            if first.cluster then output;    **** lowest ratio;

       proc print;   run;