DATA Step, Macro, Functions and more

How to manipulate data with the same variable values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

How to manipulate data with the same variable values

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


Accepted Solutions
Solution
‎10-03-2017 01:36 PM
Super Contributor
Super Contributor
Posts: 265

Re: How to manipulate data with the same variable values

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. 

View solution in original post


All Replies
Super User
Posts: 13,084

Re: How to manipulate data with the same variable values

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

Occasional Contributor
Posts: 19

Re: How to manipulate data with the same variable values

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
Super User
Posts: 13,084

Re: How to manipulate data with the same variable values


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?

 

Super Contributor
Posts: 320

Re: How to manipulate data with the same variable values

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;

Super Contributor
Posts: 320

Re: How to manipulate data with the same variable values

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

Occasional Contributor
Posts: 19

Re: How to manipulate data with the same variable values

I'm sorry ,I was answering to ballardw... I have not change anything... ?
Solution
‎10-03-2017 01:36 PM
Super Contributor
Super Contributor
Posts: 265

Re: How to manipulate data with the same variable values

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. 

Occasional Contributor
Posts: 19

Re: How to manipulate data with the same variable values

Thank you!!! It works!
Super User
Posts: 22,874

Re: How to manipulate data with the same variable values

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


Occasional Contributor
Posts: 19

Re: How to manipulate data with the same variable values

Thanks! it works to!!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 203 views
  • 5 likes
  • 5 in conversation