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.
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
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.
proc tabulate will give you what you want. You can define columns and rows easily.
Look at Example 2 here:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
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;
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;
@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
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;
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
Art,
This worked like a charm! Thanks so much for the reply and help on the problem!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.