04-20-2016 01:13 PM - edited 04-20-2016 01:14 PM
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 ->
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.
I can reply your questions, if I didn’t tell my question clearly.
04-20-2016 01:21 PM
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.
04-20-2016 01:37 PM
Why are you reading numeric values as character and then attempting arithmetic?
I think you need to clarify what is meant by:
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 - (V - v); do i = 1 to (dim(v) -1); v[i] = v[i+1]; end; V=strip(temp); /* the character to numeric, arithmetic and conversion back to character introduces leading spaces*/ end; Else if year=3 then do; temp = v - (V - v); temp2 = v - (V - v); /* in case there might be a different assignment*/ do i = 1 to (dim(v) -2); v[i] = v[i+2]; end; v = strip(temp); v = temp2; end; drop i temp: ; run;
04-20-2016 06:34 PM
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 ?
04-20-2016 06:40 PM
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.
04-20-2016 06:48 PM - edited 04-20-2016 06:49 PM
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
04-20-2016 11:40 PM
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;