topic Re: Chaging value in range of variables in big file in SAS Programming

Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 21:02:37 GMT

Hello,
it's 12hours since i'm trying to get over this problem. I need to write a report about credit scorring.

There are values assigned as 'M' that represent missing data(some comedian did that). I need to get rid of both usual missing data which is "." and "M" so i can start calculating correlations and regression based on regular data. I tried to reasign those "M" values into regular ' ' in a range of variables that contain the "M" value but i'm constantly failing i have egsams soon and have to write this report till saturday but since 3 days i have about 8 lines.
This is my recent ceration but it fails:

data wanted;
set have( keep=act_age -- app_char_cars);
if (act_age -- app_char_cars)='M' then (act_age -- app_char_cars)=' ';
run;

Re: Chaging value in range of variables in big file

novinosrin — Mon, 18 Jan 2021 21:24:52 GMT



data wanted;
set have( keep=act_age -- app_char_cars);
array t  act_age -- app_char_cars;
do over t;
 if t= 'M' then t=' ';
end;
run;

Hi @Antoni -

1. Group the list of variables in an array. This assumes all the variables in the list are of the same type and the arrangement is contiguous

2. Cycle through the array using a Loop. Yes, it's a linear search and replace method. Therefore, If an element aka variable in the array is "M", reset the value of the element to ' '

Re: Chaging value in range of variables in big file

Astounding — Mon, 18 Jan 2021 22:02:22 GMT

Chances are your data is fine and needs no change. The "comedian" may have actually known what (s)he was doing.

If you intend to run correlations, these variables must be numeric. So what appears as "M" is actually a special form of a missing value. SAS supports 28 different forms of missing values for numeric variables, and "." is only one of those. So are .A, .B, ..., .Z, and ._

Using a special missing value lets you differentiate why the value is missing, but it is still missing. So if all this rings true, let your data alone! It's fine. Try running a correlation and see what happens.

Re: Chaging value in range of variables in big file

Reeza — Mon, 18 Jan 2021 22:21:15 GMT

If you want to perform the same action on multiple columns and the columns are all the same type then you can use an array.

However, you want to do numeric calculations on your data but it has to be coded as character to contain an M or you're using special missing values. I'm going to assume the first is the situation. You can try and deal with the M or you can convert everything to numbers. Because you are converting the variable type you'll need two arrays, one for the old values and one for the new values.

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

For this to work for you, you will need to list the variables in each array. I've included a tutorial on arrays above.

data want;
set have;

array _old(*) act_age app_char_cars;
array _new(*) age char_cars;

do i=1 to dim(old_variables);

*if not missing or M, convert to a numeric value;
if old(i) not in ("", "M") then new(i) = input(old(i), 8.);

end;

drop act_age app_char_cars;
run;

@Antoni wrote:

Hello,
it's 12hours since i'm trying to get over this problem. I need to write a report about credit scorring.

There are values assigned as 'M' that represent missing data(some comedian did that). I need to get rid of both usual missing data which is "." and "M" so i can start calculating correlations and regression based on regular data. I tried to reasign those "M" values into regular ' ' in a range of variables that contain the "M" value but i'm constantly failing i have egsams soon and have to write this report till saturday but since 3 days i have about 8 lines.
This is my recent ceration but it fails:

data wanted;
set have( keep=act_age -- app_char_cars);
if (act_age -- app_char_cars)='M' then (act_age -- app_char_cars)=' ';
run;

Re: Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 22:31:27 GMT

the problem is there are 2300 variables, whats why i needed it to be done through range

Re: Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 22:34:30 GMT

it is. If i would want to delete variables with more than 40% missing values, both ' ' and 'M' it would require some kind of macro to be written, right?

Re: Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 22:38:08 GMT

the problem is i am completly new to this kid of operations and it's easier to me to have only numerical values because i won't have to deal with it through many other operations that needs to be done

Re: Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 22:39:14 GMT

ERROR: All variables in array list must be the same type, i.e., all numeric or character. - well that's not new sight for me 😞

Re: Chaging value in range of variables in big file

Reeza — Mon, 18 Jan 2021 22:41:54 GMT

There are other ways if you want to explain your problem thoroughly. We can only have the information you post and at the moment I suspect you need to back up and redesign however you created this data. If you read it in from text files or from a DB there may be methods there to ensure that these issues never occurred in the first place.

Re: Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 22:47:33 GMT

well this data was provided by my professor, i can do whatever i want with it as long as it works and provides some kind of information

Re: Chaging value in range of variables in big file

ChrisNZ — Mon, 18 Jan 2021 22:58:15 GMT

> it's easier to me to have only numerical values

You already have numerical values. Read @Astounding 's reply again. Chances are you need to fix exactly nothing.

Re: Chaging value in range of variables in big file

Antoni — Mon, 18 Jan 2021 23:01:42 GMT

and what if i want to delete variables that have more than 40% missing data? i would have to create macro?

Re: Chaging value in range of variables in big file

Reeza — Mon, 18 Jan 2021 23:30:01 GMT

I'm not sure a macro actually helps that much here.

That being said, here's an example of a macro that would do it. There's a small example at the end that shows how it's used as well and it drops anything over 20% in that example. I included that as a parameter so it's actually a more generic program. If your list ends up being longer than 64k characters this may not work as well for you.




%macro drop_missing_pct(input_dsn = , output_dsn=, pct = , id_vars=);

*create format for missing;
proc format;
    value $ missfmt ' '="Missing" other="Not Missing";
    value nmissfmt .="Missing" other="Not Missing";
run;

*Proc freq to count missing/non missing;
ods select none;
*turns off the output so the results do not get too messy;
ods table onewayfreqs=temp;

proc freq data=&INPUT_DSN. (drop = &ID_Vars);
    table _all_ / missing;
    format _numeric_ nmissfmt. _character_ $missfmt.;
run;


ods select all;
*Format output;

data long;
    length variable $32. variable_value $50.;
    set temp;
    Variable=scan(table, 2);
    Variable_Value=strip(trim(vvaluex(variable)));
    presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
    keep variable variable_value frequency percent cum: presentation;
    label variable='Variable' variable_value='Variable Value';
run;

*not required for display purposes;
proc sort data=long;
    by variable;
run;

*select variables more than x% missing;
proc sql noprint;
select variable into :drop_var_list separated by " "
from long where variable_value = 'Missing' and percent > &pct;
quit;

*Drop variables;
data &output_dsn;
set &input_dsn;
drop &drop_var_list;
run;

*clean up;
*uncomment after testing;
/* proc sql; */
/* drop table long; */
/* drop table temp; */
/* quit; */

%mend;

And the test to show it works:


*test;

data class;
    set sashelp.class;

    if age=14 then
        call missing(height, weight, sex);

    if name='Alfred' then
        call missing(sex, age, height);
    label age="Fancy Age Label";
run;


%drop_missing_pct(input_dsn = class, output_dsn = want, pct = 20, id_vars = Name);



*check output;
proc contents data=want;
run;

https://gist.github.com/statgeek/feedc3fc520cb0d2018ca2a8cab241d8

Re: Chaging value in range of variables in big file

Astounding — Tue, 19 Jan 2021 01:02:01 GMT

Given that there might be special missing values in the data, this line in the macro should be revised:

    value nmissfmt .="Missing" other="Not Missing";

It should become:

    value nmissfmt  ._ - .Z ="Missing" other="Not Missing";

Re: Chaging value in range of variables in big file

novinosrin — Tue, 19 Jan 2021 01:11:43 GMT

@Antoni Hence the reason I mentioned "This assumes all the variables in the list are of the same type and the arrangement is contiguous"

Either way, If you are running against time for a deliverable I suggest not to be concerned about the mechanisms. Please post a clean representative mock sample of what you HAVE and what you WANT. I'm sure somebody will provide the needed solution. You need to include the data types and other info so that you can avoid going back and forth.

Just act fast, get the report delivered and then open up a new thread to discuss & learn. Chill

Re: Chaging value in range of variables in big file

Antoni — Tue, 19 Jan 2021 15:00:25 GMT

yes and here's one otf the problem i'm dealing with that i could not find a solution for

drop_missing_pct(input_dsn = , output_dsn=, pct = , id_vars=)

i'm unable to put id_vars= because i need the results from all of the 2300 variables, so i need to either put them as range, write them separately or ther's other solution that im not aware of, please be patient im very new to those things and there were 6 lessons where we made really basic things and now we need to create this kind of report..

Re: Chaging value in range of variables in big file

Reeza — Tue, 19 Jan 2021 16:06:58 GMT

ID_VARS are for variables you DO NOT want included, not those you want to include. Did you try running the code? Does it not do what you want?

Re: Chaging value in range of variables in big file

Antoni — Tue, 19 Jan 2021 17:48:30 GMT

it showed this error,

ERROR: Invalid macro parameter name 4. It should be a valid SAS identifier no longer than 32 characters.
ERROR: A dummy macro will be compiled.

on this line:
length variable $32. variable_value $50.;

i tried to change "variable value %50" to 32 but its still the same

Re: Chaging value in range of variables in big file

Reeza — Tue, 19 Jan 2021 18:16:09 GMT

Did my example code not work? Did you run it exactly as posted?

Show the log from the code I provided and then show the code AND log you submitted for your data set.

Re: Chaging value in range of variables in big file

Antoni — Tue, 19 Jan 2021 18:47:15 GMT

all i did was filling: "%macro drop_missing_pct(input_dsn= have, output_dsn=want, pct = 0,4 , id_vars=);"

my code is:

data surowe_train;
set 'C:\Users\Mateusz\Documents\Biblioteki\Raport\ABT_beh_sas\abt_sam_beh_train';
run;

data surowe_valid;
set 'C:\Users\Mateusz\Documents\Biblioteki\Raport\ABT_beh_sas\abt_sam_beh_valid';
run;

proc sql noprint;
create table sklejone as
select a.*, b.* from surowe_train as a left join surowe_valid as b
On a.cid=b.cid;
quit;

log from it is:

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 data surowe_train;
29 set 'C:\Users\Mateusz\Documents\Biblioteki\Raport\ABT_beh_sas\abt_sam_beh_train';
30 run;

NOTE: There were 52841 observations read from the data set
C:\Users\Mateusz\Documents\Biblioteki\Raport\ABT_beh_sas\abt_sam_beh_train.
NOTE: The data set WORK.SUROWE_TRAIN has 52841 observations and 2310 variables.
NOTE: Instrukcja DATA zajęła (całkowity czas przetwarzania):
real time 9.87 seconds
cpu time 3.18 seconds

31
32 data surowe_valid;
33 set 'C:\Users\Mateusz\Documents\Biblioteki\Raport\ABT_beh_sas\abt_sam_beh_valid';
34 run;

NOTE: There were 53070 observations read from the data set
C:\Users\Mateusz\Documents\Biblioteki\Raport\ABT_beh_sas\abt_sam_beh_valid.
NOTE: The data set WORK.SUROWE_VALID has 53070 observations and 2310 variables.
NOTE: Instrukcja DATA zajęła (całkowity czas przetwarzania):
real time 9.49 seconds
cpu time 3.32 seconds

35
36 proc sql noprint;
37 create table sklejone as
38 select a.*, b.* from surowe_train as a left join surowe_valid as b
39 On a.cid=b.cid;

then there is a few thousand warnings about data already existing in WORK.SKLEJONE>.(it has to do with my attempts to properly merge two files, i propaby created new library).

theres no log from your code because it had error(i suppose that is the reason, because i can olnly see error beside whole code in the log)