Combining several row into one base on multiple cell values

[xiaoyao026]

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.

[RW9]

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;

[ballardw]

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.

[Astounding]

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;

[Tom]

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.