topic Re: Combining several row into one base on multiple cell values in SAS Enterprise Guide

Combining several row into one base on multiple cell values

xiaoyao026 — Tue, 28 Nov 2017 14:43:34 GMT

hello everyone

would like to get your guidance regarding how to put a code via SASprogram, to achieve below purpose.

Month	FG	Importer	Dealer	Refno	Job Number	Result 1	Result 2	Result 3	Result 4	Result 5
2015-07	4313	5959	2100	101540	1	1	1	1	0	0
2015-07	4313	5959	2100	101540	1	1	1	1	1	0

i want above 2 rows combine into one row if first 6 column data is same, then last 5 column figure should be combined as well with multi result (for example "result 4", one row is 0, the other is 1, then combined result should be 0).

would it be ok to provide your guidance?

thank you in advance.

Re: Combining several row into one base on multiple cell values

RW9 — Tue, 28 Nov 2017 15:09:24 GMT

Per your given example, you could just output last.jobno based on grouping by those 6 varaibles. I assume however that your data could have 1 then , e.g. result could be 1 then 0? If so then you need to be clear about what your rules are. What if multiple records contain 1, should it still be one? If so something like:

proc sql;
  create table WANT as
  select     MONTH,
             FG,
             IMPORTER,
             DEALER,
             REFNO,
             JOB_NO,
             case when sum(RESULT1) > 0 then 1 else 0 end as RESULT1,
             case when sum(RESULT2) > 0 then 1 else 0 end as RESULT2,
             case when sum(RESULT3) > 0 then 1 else 0 end as RESULT3,
             case when sum(RESULT4) > 0 then 1 else 0 end as RESULT4,
             case when sum(RESULT5) > 0 then 1 else 0 end as RESULT5
  from       HAVE
  group by   MONTH,FG,IMPORTER,DEALER,REFNO,JOB_NO;
quit;

Re: Combining several row into one base on multiple cell values

ballardw — Tue, 28 Nov 2017 15:10:22 GMT

To answer this question you will need to provide the structure your current data has.

Best is to provide example data in the form of a data step so we know exactly what type of variables you are working with and names and values. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Combining several row into one base on multiple cell values

Astounding — Tue, 28 Nov 2017 15:18:26 GMT

A very straightforward method ...

If it's not already in order, sort your data:

proc sort data=have;

by month FG Importer Dealer Refno Job_Number;

run;

Then get the minimum value for each variable:

proc summary data=have;

by month FG Importer Dealer Refno Job_Number;

var result1-result5;

output out=want (drop=_type_ _freq_) min=;

run;

Re: Combining several row into one base on multiple cell values

Tom — Tue, 28 Nov 2017 15:19:53 GMT

Sounds like you want to calculate the MIN() for the RESULTx variables.

proc summary missing nway data=have ;
  class month fg importer dealer refno jobnumber ;
  var result1-result5 ;
  output out=want(drop=_type_ _freq_)  min= ;
run;

If the data is sorted you can use BY instead of CLASS and it should save processing time.