Solved
New Contributor
Posts: 4

# Combining multiple rows (without missings) into one row based on a variable

Hi all,

I have a dataset that has multiple observations from a given household and I need it combined into one row per household. The problem is that the variables are the same for each observation so I can't use some of the other suggestions I have seen.

The data is currently set up like this:

 HH risk_grp Positive 1 1-4 Yrs 1 2 1-4 Yrs 0 2 5-14 Yrs 1 3 5-14 Yrs 1 3 >14 Yrs 0 4 >14 Yrs 0 5 1-4 Yrs 1 5 5-14 Yrs 1 5 >14 Yrs 1

And I am hoping for something like this:

 HH risk_grp1 Positive_1 risk_grp2 Positive_2 risk_grp3 Positive_3 1 1-4 Yrs 1 . . . . 2 1-4 Yrs 0 5-14 Yrs 1 . . 3 5-14 Yrs 1 >14 Yrs 0 . . 4 >14 Yrs 0 . . . . 5 1-4 Yrs 1 5-14 Yrs 1 >14 Yrs 1

Any help would be greatly appreciated! Also I am using SAS University Edition.

Accepted Solutions
Solution
‎03-23-2018 09:06 AM
Super User
Posts: 8,220

## Re: Combining multiple rows (without missings) into one row based on a variable

If you download and run the macro you will find at: https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

your problem is easy to solve:

```%transpose(data=have,out=want,by=hh,var=risk_grp Positive)
```

Art, CEO, AnalystFinder.com

All Replies
Super User
Posts: 13,950

## Re: Combining multiple rows (without missings) into one row based on a variable

You might want to tell us what that resulting data set will be used for. Many times that "wide" format is much harder to work with.

If the purpose is to create a report that people will read then going directly to one of the report procedures such a proc report or tabulate may work better.

Best is to post example data in the form of a data step. 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.

Regular Contributor
Posts: 238

## Re: Combining multiple rows (without missings) into one row based on a variable

proc tabulate will give you what you want.  You can define columns and rows easily.

Super User
Posts: 24,028

PROC Star
Posts: 635

## Re: Combining multiple rows (without missings) into one row based on a variable

[ Edited ]

data have;
infile datalines dlm=" " dsd;
input HH risk_grp & \$10. Positive;
datalines;
1 1-4 Yrs 1
2 1-4 Yrs 0
2 5-14 Yrs 1
3 5-14 Yrs 1
3 >14 Yrs 0
4 >14 Yrs 0
5 1-4 Yrs 1
5 5-14 Yrs 1
5 >14 Yrs 1
;
run;

proc transpose data=have out=want1(Drop=_name_) prefix=Positive_;
by HH;
var Positive;
run;
proc transpose data=have out=want2(Drop=_name_) prefix=risk_grp_;
by HH;
var risk_grp;
run;
DATA WANT;
MERGE Want1 Want2;
by HH;
run;

Thanks,
Suryakiran
Regular Contributor
Posts: 238

## Re: Combining multiple rows (without missings) into one row based on a variable

[ Edited ]

That doesn't work, but this does (always test your solution before posting it):

data have;
infile datalines dlm=" " dsd;
input Positive HH risk_grp \$10. ;
datalines;
1 1 1-4 Yrs
0 2 1-4 Yrs
1 2 5-14 Yrs
1 3 5-14 Yrs
0 3 >14 Yrs
0 4 >14 Yrs
1 5 1-4 Yrs
1 5 5-14 Yrs
1 5 >14 Yrs
;
run;

proc transpose data=have out=want1(Drop=_name_) prefix=Positive_;
by HH;
var Positive;
run;
proc transpose data=have out=want2(Drop=_name_) prefix=risk_grp_;
by HH;
var risk_grp;
run;
DATA WANT;
MERGE Want1 Want2;
by HH;
run;

Frequent Contributor
Posts: 105

## Re: Combining multiple rows (without missings) into one row based on a variable

[ Edited ]

@SuryaKiran  Your proposed solution on the thread https://communities.sas.com/t5/Base-SAS-Programming/way-to-simplyfy-the-code/m-p/447544#M112453  doesn't work either as you have gotten the macro execution and sas execution comepletely wrong. I don't want mean to sound strong/harsh but I have to concur with @tomrvincent  in the objective that it may mislead for the OP and other readers

Kindly test or just outline the approach

Super User
Posts: 2,078

## Re: Combining multiple rows (without missings) into one row based on a variable

``````data have;
infile datalines dlm=" " dsd;
input Positive HH risk_grp \$10. ;
datalines;
1 1 1-4 Yrs
0 2 1-4 Yrs
1 2 5-14 Yrs
1 3 5-14 Yrs
0 3 >14 Yrs
0 4 >14 Yrs
1 5 1-4 Yrs
1 5 5-14 Yrs
1 5 >14 Yrs
;
run;

data want;
do _n_=1 by 1 until(last.hh);
set have;
by hh;
array Positive_(3) ;/*subscript value to be modified for real dataset*/
array risk_grpp(3) \$25;/*subscript value to be modified for real dataset*/
if first.hh then call missing(of Positive_(*),of risk_grpp(*));
Positive_(_n_)=Positive;
risk_grpp(_n_)=risk_grp;
end;
drop risk_grp Positive;
run;``````

Solution
‎03-23-2018 09:06 AM
Super User
Posts: 8,220

## Re: Combining multiple rows (without missings) into one row based on a variable

If you download and run the macro you will find at: https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

your problem is easy to solve:

```%transpose(data=have,out=want,by=hh,var=risk_grp Positive)
```

Art, CEO, AnalystFinder.com

New Contributor
Posts: 4

## Re: Combining multiple rows (without missings) into one row based on a variable

Art,

This worked like a charm! Thanks so much for the reply and help on the problem!

Super User
Posts: 10,860

## Re: Combining multiple rows (without missings) into one row based on a variable

``````data have;
infile datalines dlm=" " dsd;
input Positive HH risk_grp \$10. ;
datalines;
1 1 1-4 Yrs
0 2 1-4 Yrs
1 2 5-14 Yrs
1 3 5-14 Yrs
0 3 >14 Yrs
0 4 >14 Yrs
1 5 1-4 Yrs
1 5 5-14 Yrs
1 5 >14 Yrs
;
run;
proc sql noprint;
select max(n) into : n
from (select count(*) as n from have group by hh);
quit;
proc summary data=have;
by hh;
output out=want idgroup(out[&n] (risk_grp Positive)=);
run;
proc print noobs;run;``````
☑ This topic is solved.