BookmarkSubscribeRSS Feed
sahilkh18
Calcite | Level 5

Hello

I have a dataset with a lot of observations in it. I want to match the type, date and time, and if everything is a match, then sum up the first occurrence of each column D, E and F.

I have attached the sample data and the desired output. I need to match the data in row # 2 and row # 3 (columns B, C and D) and basis that sum up the values in the remaining columns in a cumulative manner (desired output shown). I tried using the first.variable, but it did not give me the desired output. Please help. 

 

What i tried doing was this:

 

data s;

set test;

conc = cats(type, date, time); 'this way i can get the final value to match'

if first.conc = 1 then d = d+1;

else d=d;

if first.conc = 1 then e = e+1;

else e=e;

run;

 

It does not give me the desired output i need. 

6 REPLIES 6
gamotte
Rhodochrosite | Level 12

Please post your example data in the form of a datastep.

In order to use first.var, your dataset has to be sorted by var. First create your conc variable and sort your dataset by conc then you can modify d and e variables.

 

Edit :don't forget to add a by statement in your data step :

by conc;

sahilkh18
Calcite | Level 5
I made the code in SAS for reference along with data lines.

There is a column within the datalines which shows the expected desired output also. I want to perform this operation for multiple numeric columns (as shown in Desired output)



data s;
input A $ Type $ Date mmddyy10. time time. d desired_op;
format date mmddyy10. time time.;
datalines;
Test ANC 9/13/2018 8:15:00 1 2
Test ANC 9/13/2018 8:15:00 1 1
Test ANC 9/13/2018 8:45:00 2 5
Test ANC 9/13/2018 8:45:00 3 3
Test DFL 9/13/2018 9:15:00 4 9
Test DFL 9/13/2018 9:15:00 5 5
Test DFL 9/13/2018 9:45:00 6 13
Test DFL 9/13/2018 9:45:00 7 7
;
run;

data test;
set s;
conc = cats(type, date, time);
run;

proc sort data =test out = test1;
by conc;
run;

data test2;
set test1;
by conc;
if first.conc = 1 then d = d+d;
else d = d;
run;
andreas_lds
Jade | Level 19

Please post the data as data-step using datalines statement so we can see the data in the same way your sas-session sees it. Excel-files are not suitable for transporting data.

sahilkh18
Calcite | Level 5

I made the code in SAS for reference along with data lines. 

There is a column within the datalines which shows the expected desired output also. I want to perform this operation for multiple numeric columns (as shown in Desired output)

 

data s;
input A $ Type $ Date mmddyy10. time time. d desired_op;
format date mmddyy10. time time.;
datalines;
Test ANC 9/13/2018 8:15:00 1 2
Test ANC 9/13/2018 8:15:00 1 1
Test ANC 9/13/2018 8:45:00 2 5
Test ANC 9/13/2018 8:45:00 3 3
Test DFL 9/13/2018 9:15:00 4 9
Test DFL 9/13/2018 9:15:00 5 5
Test DFL 9/13/2018 9:45:00 6 13
Test DFL 9/13/2018 9:45:00 7 7
;
run;

data test;
set s;
conc = cats(type, date, time);
run;

proc sort data =test out = test1;
by conc;
run;

data test2;
set test1;
by conc;
if first.conc = 1 then d = d+d;
else d = d;
run;

Astounding
PROC Star

Your program doesn't really sum up anything.  To do that, you will need to create a new variable.  For example:

 

if first.conc then total_d + d;

 

That change might be enough to get the result you are looking for.

ballardw
Super User

@sahilkh18 wrote:

I made the code in SAS for reference along with data lines. 

There is a column within the datalines which shows the expected desired output also. I want to perform this operation for multiple numeric columns (as shown in Desired output)

 

data s;
input A $ Type $ Date mmddyy10. time time. d desired_op;
format date mmddyy10. time time.;
datalines;
Test ANC 9/13/2018 8:15:00 1 2
Test ANC 9/13/2018 8:15:00 1 1
Test ANC 9/13/2018 8:45:00 2 5
Test ANC 9/13/2018 8:45:00 3 3
Test DFL 9/13/2018 9:15:00 4 9
Test DFL 9/13/2018 9:15:00 5 5
Test DFL 9/13/2018 9:45:00 6 13
Test DFL 9/13/2018 9:45:00 7 7
;
run;

data test;
set s;
conc = cats(type, date, time);
run;

proc sort data =test out = test1;
by conc;
run;

data test2;
set test1;
by conc;
if first.conc = 1 then d = d+d;
else d = d;
run;


This works for the example data. No need to add variables. It should work if you have more than 2 rows for Type/date/time combinations.

data s;
   input A $ Type $ Date mmddyy10. time time. d desired_op;
   format date mmddyy10. time time.;
datalines;
Test ANC 9/13/2018 8:15:00 1 2
Test ANC 9/13/2018 8:15:00 1 1
Test ANC 9/13/2018 8:45:00 2 5
Test ANC 9/13/2018 8:45:00 3 3
Test DFL 9/13/2018 9:15:00 4 9
Test DFL 9/13/2018 9:15:00 5 5
Test DFL 9/13/2018 9:45:00 6 13
Test DFL 9/13/2018 9:45:00 7 7
;
run;
proc sort data=s;
 by type date time;
run;


proc summary data=s nway;
   class type date time;
   var d;
   output out=s_sum (drop= _:) sum=;
run;

data test2;
   merge s
         s_sum
   ;
 by type date time;
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 870 views
  • 0 likes
  • 5 in conversation