How to manipulate data with the same variable values

[bemariec]

Hi!

See the data set bellow to understand the variables I'm trying to work with

data TEST;
input Ref    Date$    Cycle    side$    value;
datalines;
024301 2017-04-03 1 A 39.969
024301 2017-04-03 1 B 40.645
024301 2017-04-03 1000 A 44.353
024301 2017-04-03 1000 B 44.788
024301 2017-04-03 10000 A 52.774
024301 2017-04-03 10000 B 47.719
024301 2017-04-03 30000 A 53.998
024301 2017-04-03 30000 B 51.878
;
run;

I have about 4000 observations. Every Ref has 8 observations: cycle 1 (A and B), cycle 1000 (A and B), cycle 10 000 (A and B) and cycle 30 000 (A and B). Here's what I want to do for every different Ref number (about 800 different Ref numbers):

- take the Value with cycle= 1 and side =A  (same for Side B) to be stored in a new variable "CYCLE1_VALUE" beside all the other cycles with the same Ref, same Date and same Side. See the table bellow for an illustration of what I want:

data WANT;
input Ref    Date$    Cycle    side$    value     CYCLE1_VALUE;
datalines;
024301 2017-04-03 1 A 39.969    39.969
024301 2017-04-03 1 B 40.645    40.645
024301 2017-04-03 1000 A 44.353    39.969
024301 2017-04-03 1000 B 44.788    40.645
024301 2017-04-03 10000 A 52.774    39.969
024301 2017-04-03 10000 B 47.719    40.645
024301 2017-04-03 30000 A 53.998     39.969
024301 2017-04-03 30000 B 51.878     40.645
;
run;

I need to do this for the 800 different Ref numbers... There are no regularities in the ref numbers, unfortunately... 

I will need to do easy math after that! How can I do that?

Thanks!

Marie-Christine

[ballardw]

Can you describe what your are going to do "in the easy math" afterwards? It may be that there is much easier approach or at least more straightforward.

Quite often an approach that manipulates data in the manner you are showing is following an approach developed from something like Excel because of the limitations of spreadsheets and tools in SAS provide considerably different ways to summarize and combine data

[bemariec]

I want to do a substraction between Value and Value at cycle 1 for every Ref, this will by the variable that I will work with to do mean and standard deviation

[ballardw]

---

@bemariec wrote:
I want to do a substraction between Value and Value at cycle 1 for every Ref, this will by the variable that I will work with to do mean and standard deviation

---

By any chance does this represent that difference?

data want;
   set test;
   lc=lag(cycle);
   dv=dif(value);
   if cycle=lc then Cycledif = dv;
   drop lc dv;
run;

Then proc means with cycle as a class variable and dv as the var?

[KachiM]

It looks very much similar to the following algorithm:

[1] If Cycle = 1

            if side = A then holdA_Value = A_Value; Cycle1_Value = holdA_Value; 

           else if side = B then holdB = B_Value; Cycle1_Value = holdB_Value;

[2] If Cycle ^= 1

           if side = A  then Cycle1_Value = holdA_Value;

           if side = B then Cycle1_Value = holdB_Value;

[KachiM]

When you post, you should have thought everything. Do not change the specs afterwards. Time is precious to everyone.

[bemariec]

I'm sorry ,I was answering to ballardw... I have not change anything... ?

[HB · Accepted solution]

I agree with @ballardw that knowing where you ultimately want to go woudl help in finding you the best solution.

In the meantime, if you do this:

data TEST;
input Ref    Date:yymmdd10.    Cycle    side$    value;
datalines;
024301 2017-04-03 1 A 39.969
024301 2017-04-03 1 B 40.645
024301 2017-04-03 1000 A 44.353
024301 2017-04-03 1000 B 44.788
024301 2017-04-03 10000 A 52.774
024301 2017-04-03 10000 B 47.719
024301 2017-04-03 30000 A 53.998
024301 2017-04-03 30000 B 51.878
;
run;

proc sql;
	create table populated as
	select c.ref, c.date, c.cycle, c.side, c.value as myval, d.value as fixval
	from test c inner join test d
	on c.ref = d.ref and c.date = d.date and c.side = d.side
	where d.cycle = 1;
quit; 

You get this:

The SAS System					
					
Ref	Date	Cycle	side	myval	fixval
24301	20912	1	A	39.969	39.969
24301	20912	1	B	40.645	40.645
24301	20912	1000	A	44.353	39.969
24301	20912	1000	B	44.788	40.645
24301	20912	10000	A	52.774	39.969
24301	20912	10000	B	47.719	40.645
24301	20912	30000	A	53.998	39.969
24301	20912	30000	B	51.878	40.645

But as noted, that may not really be the way you want to go. 

[bemariec]

Thank you!!! It works!

[Reeza]

@bemariec your sample data is bad. If you have multiple REF and that's important you should include sample data with multiple REFs so we can replicate your situation. I've made some assumptions and replicated your REF to create at least two in my data. 

*sample data;
data TEST;
input Ref    Date$    Cycle    side$    value;
datalines;
024301 2017-04-03 1 A 39.969
024301 2017-04-03 1 B 40.645
024301 2017-04-03 1000 A 44.353
024301 2017-04-03 1000 B 44.788
024301 2017-04-03 10000 A 52.774
024301 2017-04-03 10000 B 47.719
024301 2017-04-03 30000 A 53.998
024301 2017-04-03 30000 B 51.878
024302 2017-04-03 1 A 45.234
024302 2017-04-03 1 B 40.645
024302 2017-04-03 1000 A 44.353
024302 2017-04-03 1000 B 44.788
024302 2017-04-03 10000 A 52.774
024302 2017-04-03 10000 B 47.719
024302 2017-04-03 30000 A 53.998
024302 2017-04-03 30000 B 51.878
;
run;

*isolate cycle1;
/*if you want to do this for multiple variables it may be better to
use a transpose to get the data you need*/
data cycle1;
set test;
where cycle=1 and side='A';
keep ref value;
rename value=Cycle1_Value;
run;

*sort for merge;
proc sort data=test;
by ref date cycle side;
run;

proc sort data=cycle1;
by ref;
run;

*merge data together;
data want; 
merge test cycle1;
by ref;
run;

[bemariec]

Thanks! it works to!!