topic Re: Data Manipulation in SAS Data Management

Data Manipulation

Ujjawal — Sat, 07 Mar 2015 10:02:34 GMT

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!

Re: Data Manipulation

LinusH — Sat, 07 Mar 2015 10:30:37 GMT

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

Re: Data Manipulation

shubhayog — Sat, 07 Mar 2015 13:32:47 GMT

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

Re: Data Manipulation

LinusH — Sat, 07 Mar 2015 14:29:38 GMT

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.

Re: Data Manipulation

data_null__ — Sat, 07 Mar 2015 14:48:56 GMT

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;

Re: Data Manipulation

Jim_G — Sat, 07 Mar 2015 14:59:18 GMT

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;

Re: Data Manipulation

shubhayog — Sat, 07 Mar 2015 15:17:48 GMT

Hi,

Thank you very much for help.

Regards,

Yogesh