topic Re: How to manipulate data with the same variable values in SAS Programming

How to manipulate data with the same variable values

bemariec — Tue, 03 Oct 2017 15:47:37 GMT

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

Re: How to manipulate data with the same variable values

ballardw — Tue, 03 Oct 2017 16:05:11 GMT

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

Re: How to manipulate data with the same variable values

KachiM — Tue, 03 Oct 2017 16:38:26 GMT

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;

Re: How to manipulate data with the same variable values

bemariec — Tue, 03 Oct 2017 16:38:58 GMT

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

Re: How to manipulate data with the same variable values

KachiM — Tue, 03 Oct 2017 16:42:47 GMT

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

Re: How to manipulate data with the same variable values

bemariec — Tue, 03 Oct 2017 16:45:22 GMT

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

Re: How to manipulate data with the same variable values

HB — Tue, 03 Oct 2017 16:58:59 GMT

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.

Re: How to manipulate data with the same variable values

Reeza — Tue, 03 Oct 2017 17:15:27 GMT

@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;

Re: How to manipulate data with the same variable values

bemariec — Tue, 03 Oct 2017 17:38:45 GMT

Thank you!!! It works!

Re: How to manipulate data with the same variable values

bemariec — Tue, 03 Oct 2017 17:50:44 GMT

Thanks! it works to!!

Re: How to manipulate data with the same variable values

ballardw — Tue, 03 Oct 2017 19:33:50 GMT

@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?