topic Re: Invert values from a dataset in SAS Programming

Invert values from a dataset

jonatan_velarde — Tue, 29 Sep 2020 16:27:49 GMT

Hi friends:

I have this data set:

data have;

input var1 var2 var3;

cards;

4 2 3
3 2 3
3 2 4
4 2 4
1 3 4
2 3 2
4 4 2
1 4 4
4 4 3
3 4 3
4 4 2
2 4 2
1 4 2
2 4 5
5 1 1
2 4 4
4 3 4
5 2 3
4 2 5
5 4 3

;

i need to replace values just in var1 and var3 with this rule:

if var1 = 5 then var1 = 1

if var1 = 4 then var1 = 2

if var1 = 2 then var1 = 4

if var1 = 1 then var1 = 5;

i shod do this to all variables, but lets supose that i have 35 variables, it can be much time to do this manually.

Can anyone help pls

Thanks

Re: Invert values from a dataset

PaigeMiller — Tue, 29 Sep 2020 16:57:40 GMT

Simple math

var1=6-var1;

To do this for 35 variables, use an array

data want;
    set have;
    array v var1-var35;
    do i=1 to dim(v);
        v(i)=6-v(i);
    end;
    drop i;
run;

Re: Invert values from a dataset

jimbarbour — Tue, 29 Sep 2020 17:01:03 GMT

Hard to argue with the simplicity of @PaigeMiller's solution, but another approach using a Macro to generate SAS code:

OPTIONS	MPRINT;

%LET	Max_Value	=	5;
%LET	Min_Value	=	1;
%LET	Nbr_Vars	=	3;

**------------------------------------------------------------------------------**;

%MACRO	Generate_Inversion_Code;
	%DO	j	=	&Min_Value	%TO	&Max_Value;
		%LET	Value_Old&j	=	%EVAL(&Min_Value + &j -1);
		%LET	Value_New&j	=	%EVAL(&Max_Value - &j +1);
	%END;

	%**	Debugging Code	**;
/*	%DO	k	=	&Min_Value	%TO	&Max_Value;*/
/*		%PUT	&Nte1  &k = &&Value_Old&k;*/
/*		%PUT	&Nte2  &k = &&Value_New&k;*/
/*	%END;*/

	%DO	i	=	1	%TO	&Nbr_Vars;
		%DO	j	=	&Min_Value	%TO	&Max_Value;
			%IF	&j	=	&Max_Value	%THEN
				%DO;
					IF	Var&i	=	&&Value_Old&j	THEN
						Var&i	=	&&Value_New&j;
				%END;
			%ELSE
				%DO;
					IF	Var&i	=	&&Value_Old&j	THEN
						Var&i	=	&&Value_New&j;
					ELSE
				%END;
		%END;
	%END;
%MEND	Generate_Inversion_Code;

**------------------------------------------------------------------------------**;

DATA	Have;
	input var1 var2 var3;

	%Generate_Inversion_Code;

cards;

4 2 3
3 2 3
3 2 4
4 2 4
1 3 4
2 3 2
4 4 2
1 4 4
4 4 3
3 4 3
4 4 2
2 4 2
1 4 2
2 4 5
5 1 1
2 4 4
4 3 4
5 2 3
4 2 5
5 4 3
;
RUN;

Results:

Jim

Re: Invert values from a dataset

jonatan_velarde — Tue, 29 Sep 2020 17:01:51 GMT

awesome, this answer is very clever.

Just to be clear, how could be the current code if i just need to replave var1 and var3

thanks in advance

Re: Invert values from a dataset

PaigeMiller — Tue, 29 Sep 2020 17:03:30 GMT

@jonatan_velarde wrote:
awesome, this answer is very clever.

Just to be clear, how could be the current code if i just need to replave var1 and var3

array v var1 var3;

Re: Invert values from a dataset

jonatan_velarde — Tue, 29 Sep 2020 17:05:14 GMT

Already did this and apears whole variables in the original data

Re: Invert values from a dataset

ballardw — Tue, 29 Sep 2020 17:09:51 GMT

If this is something that you may have to do repeatedly then perhaps reading the data with a custom informat would be a better solution than fixing afterwards.

This would assume that you would typically read the data from a text file with a data step.

proc format;
invalue invert
1 = 5
2 = 4
3 = 3
4 = 2
5 = 1
;
run;

data example;
  informat x1 x2 x3 invert.;
  input x1 x2 x3;
datalines;
1 2 3
4 5 1
2 3 4
5 1 2
;