Desktop productivity for business analysts and programmers

Combining several row into one base on multiple cell values

Reply
Occasional Contributor
Posts: 12

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.

 

MonthFG Importer Dealer Refno Job Number Result 1Result 2Result 3Result 4Result 5
2015-07431359592100101540111100
2015-07431359592100101540111110

 

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
Super User
Posts: 8,174

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: 11,558

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: 5,610

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
Super User
Posts: 7,154

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.

Ask a Question
Discussion stats
  • 4 replies
  • 121 views
  • 0 likes
  • 5 in conversation