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!
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
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
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.
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.
Hi,
Thank you very much for help.
Regards,
Yogesh
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.