turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Combining several row into one base on multiple ce...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

2 weeks ago

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

2 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

2 weeks ago

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to xiaoyao026

2 weeks ago

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.