## Combining several row into one base on multiple cell values

Occasional Contributor
Posts: 18

# Combining several row into one base on multiple cell values

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.

Super User
Posts: 9,402

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

Posted in reply to xiaoyao026

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;
```
Super User
Posts: 13,304

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

Posted in reply to xiaoyao026

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-dat... 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.

Super User
Posts: 6,629

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

Posted in reply to xiaoyao026

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;

Super User
Posts: 7,932

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

Posted in reply to xiaoyao026

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.

Discussion stats
• 4 replies
• 150 views
• 0 likes
• 5 in conversation