DATA Step, Macro, Functions and more

How to Assign Variables to Each Other by Using SAS Arrays

Reply
Super Contributor
Posts: 395

How to Assign Variables to Each Other by Using SAS Arrays

[ Edited ]

Hello everyone,

 

Nowadays, I’m trying to generate a difficult algorithm to create desired output. I have sample data set as below. There are six “Value” variables and there is a “Year” variable . I think, I need a macro or array statement to assign following variables to each other. My purpose is that if “Year” variable is “2” then it should be -> Value1=Value2, Value2=Value3, Value3=Value4, Value4=Value5, Value5=Value6, lastly, when it is come the Value6 variable it should be Value6=Value6-(Value5-Value6).

 

If “Year” Variable is “3” then it should be -> Value1=Value3, Value2=Value4,Value3=Value5,Value4=Value6, lastly, when it comes Value5 and Value6 it should be ->

Value5=Value6-(Value5-Value6)

Value6=Value6-(Value5-Value6).

 

I’m not so familiar with arrays so if you can help me, I’ll be glad.

 

Here is my sample data set.

 

Data Have;
Length ID $ 12 Year 8 Value1 $ 12 Value2 $ 12  Value3 $ 12  Value4 $ 12  Value5 $ 12  Value6 $ 12 ;
Infile Datalines Missover;
Input ID Year Value1 Value2 Value3 Value4 Value5 Value6;
Datalines;
001 2 0.9 0.1 0.9 0.4 0.9 0.5
002 3 0.6 0.6 0.7 0.6 0.6 0.7
003 1 0.3 0.8 0.2 0.4 0.3 0.2
004 5 0.4 0.3 0.5 0.6 0.5 0.9
;
Run;

 

And here is my desired output.

 

DesiredOutput.png

 

I can reply your questions, if I didn’t tell my question clearly.

 

Thank you

Super User
Posts: 19,870

Re: How to Assign Variables to Each Other by Using SAS Arrays

Not a macro, use arrays.

 

Define an array for your value variable list. 

 

Use year to define where your loop starts and some math to determine where it ends. And if it goes over then you assign your formula. 

 

I would suggest giving it a try first 😀 Post your code from there for more help. 

Super User
Posts: 11,343

Re: How to Assign Variables to Each Other by Using SAS Arrays

Why are you reading numeric values as character and then attempting arithmetic?

 

I think you need to clarify what is meant by:

Value6=Value6-(Value5-Value6).

For row 1  the above translates to  0.5 - (0.9-.0.5)  which would be 0.1 not the 0.9 shown in your output. Either your "have" data is wrong or the algorithm (though it does match the row 3 output).

 

Also your row 4 output bears no resemblance to the input.

This may get you started.

data want;
   set have;
   array v Value: ;
   if year = 2 then do;
      temp = v[6] - (V[5] - v[6]);
      do i = 1 to (dim(v) -1);
         v[i] = v[i+1];
      end;
      V[6]=strip(temp); /* the character to numeric, arithmetic and conversion back to character introduces leading spaces*/
   end;
   Else if year=3 then do;
      temp = v[6] - (V[5] - v[6]);
      temp2 = v[6] - (V[5] - v[6]); /* in case there might be a different assignment*/
      do i = 1 to (dim(v) -2);
         v[i] = v[i+2];
      end;
      v[5] = strip(temp);
      v[6] = temp2;
   end;
   drop i temp: ;
run;
Super Contributor
Posts: 395

Re: How to Assign Variables to Each Other by Using SAS Arrays

Hello @Reeza and @ballardw,

 

Thank you for trying to help me but it is very hard, I'm not familiar with Arrays. Lets forget about about this part -> Value6=Value6-(Value5-Value6).

 

I can benefit from @ballardw 's example but is there a more dynamic way to create this table. For example, if we have 30 years and our Values are hundreds how can we do this ?

 

Thank you,

Super User
Posts: 11,343

Re: How to Assign Variables to Each Other by Using SAS Arrays

If you have hundreds of variables in an observation that need shuffling then you need to provide a much better example.

I would be very tempted to try to have one record that has and actual calendar Year and value and then modify the year, IF I understand what you are attempting.

 

I have a sneaking suspicion that somewhere in the past that no attention was paid to aligning like data values variable by name, possibly from using PROC IMPORT on files with slightly different layouts and then combined multiple datasets.

If that is the case (or similar) it may be better in the long run to go back and consciously control the original SAS dataset creation.

Super Contributor
Posts: 395

Re: How to Assign Variables to Each Other by Using SAS Arrays

[ Edited ]

@ballardw,

 

I don't exactly understand what you are saying, I don't wanted to annoy you. Your example has already helped me, I go over your example, I just wanted to develop your example but it is okay,  thank you very much 

Super User
Posts: 19,870

Re: How to Assign Variables to Each Other by Using SAS Arrays

This isn't correct, but it's the idea. You'll need to tweak the loops to what they need to be for the situation, but hopefully it helps to illustrate the idea. It's fully scaleable, you only need to change the length of the value array.

 

data want;
set have;
array value(*) value1-value6;
end_loop=dim(value) - year;

ind=1;

do i=year to end_loop;
	value(ind)=value(i);
	ind+1;
end;

do j=ind to dim(value);
	value(j)=.;
end;
run;
Ask a Question
Discussion stats
  • 6 replies
  • 301 views
  • 0 likes
  • 3 in conversation