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
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.
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 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?
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;
When you post, you should have thought everything. Do not change the specs afterwards. Time is precious to everyone.
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 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: