topic Re: How to match observations in SAS and compute results basis the match results in SAS Programming

How to match observations in SAS and compute results basis the match results

sahilkh18 — Thu, 13 Sep 2018 07:36:07 GMT

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.

Re: How to match observations in SAS and compute results basis the match results

gamotte — Thu, 13 Sep 2018 07:58:33 GMT

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;

Re: How to match observations in SAS and compute results basis the match results

andreas_lds — Thu, 13 Sep 2018 07:58:05 GMT

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.

Re: How to match observations in SAS and compute results basis the match results

sahilkh18 — Thu, 13 Sep 2018 08:39:05 GMT

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;

Re: How to match observations in SAS and compute results basis the match results

sahilkh18 — Thu, 13 Sep 2018 08:40:07 GMT

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;

Re: How to match observations in SAS and compute results basis the match results

Astounding — Thu, 13 Sep 2018 11:24:53 GMT

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.

Re: How to match observations in SAS and compute results basis the match results

ballardw — Thu, 13 Sep 2018 15:17:24 GMT

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