BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mdz
Fluorite | Level 6 mdz
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

10 REPLIES 10
ballardw
Super User

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.

tomrvincent
Rhodochrosite | Level 12

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

SuryaKiran
Meteorite | Level 14

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
tomrvincent
Rhodochrosite | Level 12

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;

MarkWik
Quartz | Level 8

@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 

novinosrin
Tourmaline | Level 20

 

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;

 

art297
Opal | Level 21

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

 

 

mdz
Fluorite | Level 6 mdz
Fluorite | Level 6

Art,

 

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

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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