topic Re: Combining multiple rows (without missings) into one row based on a variable in SAS Programming

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

mdz — Thu, 22 Mar 2018 15:09:09 GMT

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.

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

ballardw — Thu, 22 Mar 2018 15:13:41 GMT

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-data-AKA-generate/ta-p/258712 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.

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

tomrvincent — Thu, 22 Mar 2018 15:16:27 GMT

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

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

Reeza — Thu, 22 Mar 2018 15:21:58 GMT

Look at Example 2 here:

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

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

SuryaKiran — Thu, 22 Mar 2018 15:42:00 GMT

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;

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

tomrvincent — Thu, 22 Mar 2018 16:50:03 GMT

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;

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

MarkWik — Thu, 22 Mar 2018 18:31:16 GMT

@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

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

novinosrin — Thu, 22 Mar 2018 18:46:19 GMT

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;

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

art297 — Thu, 22 Mar 2018 22:49:49 GMT

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-a-dataset/ta-p/433620

your problem is easy to solve:

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

Art, CEO, AnalystFinder.com

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

mdz — Fri, 23 Mar 2018 13:06:01 GMT

Art,

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

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

Ksharp — Fri, 23 Mar 2018 14:08:10 GMT

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;