DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor mdz
New Contributor
Posts: 4
Accepted Solution

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:

HHrisk_grpPositive 
11-4 Yrs1
21-4 Yrs0
25-14 Yrs1
35-14 Yrs1
3>14 Yrs0
4>14 Yrs0
51-4 Yrs1
55-14 Yrs1
5>14 Yrs1

 

 And I am hoping for something like this:

 

HHrisk_grp1Positive_1risk_grp2Positive_2risk_grp3Positive_3
11-4 Yrs1....
21-4 Yrs05-14 Yrs1..
35-14 Yrs1>14 Yrs0..
4>14 Yrs0....
51-4 Yrs15-14 Yrs1>14 Yrs1

 

 

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

 

 

View solution in original post


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

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

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 ]
Posted in reply to SuryaKiran

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 ]
Posted in reply to SuryaKiran

@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 mdz
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 207 views
  • 1 like
  • 9 in conversation