topic Re: how to change values for all the columns in a dataset in SAS Programming

how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 17:35:51 GMT

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!

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 17:38:57 GMT

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.

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 17:43:07 GMT

This is before:

and this is after:

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

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 17:45:52 GMT

and the original columns still contain in the dataset

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 17:50:14 GMT

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?

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 17:51:44 GMT

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.

Re: how to change values for all the columns in a dataset

Reeza — Wed, 29 Jun 2022 17:52:34 GMT

Agreed - wondering if a format may not do whatever is needed instead of needing new variables.

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 17:53:45 GMT

This is an example for one variable:

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

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 17:56:58 GMT

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

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 18:00:28 GMT

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.

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 18:03:07 GMT

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:

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 18:04:39 GMT

Example:

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

Re: how to change values for all the columns in a dataset

Reeza — Wed, 29 Jun 2022 18:04:42 GMT

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

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 18:26:26 GMT

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!

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 18:29:03 GMT

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

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

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 18:43:54 GMT

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!

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 19:00:30 GMT

@lydia_li wrote:

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!

I don't know what this means.

Re: how to change values for all the columns in a dataset

lydia_li — Wed, 29 Jun 2022 19:05:25 GMT

it means I still need the solution for this:

This is before:

and this is after:

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

Re: how to change values for all the columns in a dataset

PaigeMiller — Wed, 29 Jun 2022 19:09:45 GMT

Well, if you are going to compute WOE, you do NOT need this. And I can't think of any good reason to create large numbers of columns where the values are all 1. So, I fall back to my position that just because it is possible to do such a thing does not mean you SHOULD do such a thing. Convince me that you really really really need this.

Re: how to change values for all the columns in a dataset

Reeza — Wed, 29 Jun 2022 19:12:43 GMT

Unless you're coding all columns the same, which usually isn't how you bin categories for Weight of Evidence there's no way to simplify this.
Do you have a set of categories you want to test for a set of columns? If so, you can do that via an array.

You'll need to do this separately for character or numeric set of variables.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list that can be used for arrays.
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

If you have large set of lists you can generate the new/old names dynamically by querying sashelp.vcolumn to generate them in macro variables.

data want;
set have;

array orig(5) var1 bc1 acb2 ade ref;
array new(5) new1-new5;

do i=1 to 5;
if orig(i) < 100 then new(i) = 1;
else if orig(i) < 200 then new(i) =  2;
....

end;

run;