BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bemariec
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
HB
Barite | Level 11 HB
Barite | Level 11

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

10 REPLIES 10
ballardw
Super User

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
Obsidian | Level 7
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
Super User

@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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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

bemariec
Obsidian | Level 7
I'm sorry ,I was answering to ballardw... I have not change anything... ?
HB
Barite | Level 11 HB
Barite | Level 11

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
Obsidian | Level 7
Thank you!!! It works!
Reeza
Super User

@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
Obsidian | Level 7
Thanks! it works to!!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 10 replies
  • 1206 views
  • 5 likes
  • 5 in conversation