how to change values for all the columns in a dataset

[lydia_li]

Hello, I have a dataset like this:

I want to update the values in each columns except columns with names containing bins, the value I want to assign is equal to 1, but I want to keep the original column names and re-name the new columns as bin_original column names. So how should I do that with a macro? expect I have 500 columns to modify.

Thanks!

[PaigeMiller]

Macros are probably not needed, but an ARRAY might help.

However, your description of what you want to do is not clear to me. Please provide a small example with 5 columns and 5 rows showing the data set before any change is applied, and then also showing the data set after any change is applied.

[lydia_li]

This is before:

and this is after:

no change to the last columns for example, and the name will be updated as Bin_XXX

[lydia_li]

and the original columns still contain in the dataset

[PaigeMiller]

Ok, but before I figure out how to do this, what is the reason you want columns where every row has the value of 1? This seems like an odd thing to do. What is the next step of data analysis or reporting that you would do?

[lydia_li]

yes, they only have 1 bin so after I set them to 1, I need to create a macro to calculate WOE and Information values for each of columns variables.

[lydia_li]

This is an example for one variable: 

But I should apply the function for each of the variables in the dataset

[lydia_li]

I think the format is num as I need the variable to do the calculation

[Reeza]

https://support.sas.com/resources/papers/proceedings13/095-2013.pdf

[PaigeMiller]

There are weight of evidence macros in SAS that you can find. These don't require columns of 1s. You can also compute weight of evidence in PROC HPBIN, again you don't need columns of 1s.

In addition, percent of occurence/non-occurence can be computed via PROC FREQ or PROC MEANS (and probably several other ways) without creating these columns of 1s.

So, I think you should go down one of those paths, rather than create columns of 1s.

[lydia_li]

so would you please show me how to use the following dataset to directly calculate WOE and IV if there is no need to adjust? Thanks! 

I want each table to have a fromat like this:

[PaigeMiller]

Example:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/prochp/prochp_hpbin_examples05.htm

[lydia_li]

proc hpbin data=TEMP_INT WOE BINS_META=Mapping;
ods output Mapping=Mapping;
input DelinquentDays/numbin=4;
target DelinquentDays/level=nominal order=desc;
run;

 what if I want to apply this HPBIN function to each of the column? What should I do? and what if there is some columns that I dont want to apply? maybe there is a exclusion list and all the columns not in the exclusion list need to go into the loop. is there a macro to do so? Thanks!

[PaigeMiller]

The INPUT statement can have as many variables as you want in it.

Also, TARGET must be a different variable than the INPUT variables.

[lydia_li]

thanks, but I still need the solution for changing the value for each column, not just to 1, 1 is an example, so how to do that? Thanks!