BookmarkSubscribeRSS Feed
turcay
Lapis Lazuli | Level 10

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

6 REPLIES 6
Reeza
Super User

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. 

ballardw
Super User

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;
turcay
Lapis Lazuli | Level 10

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,

ballardw
Super User

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.

turcay
Lapis Lazuli | Level 10

@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 

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1252 views
  • 0 likes
  • 3 in conversation