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