I have the following dataset (forgive the "fake" data) and want to merge the Content column (and have the data separated by a comma) based on the Type column.
Building Type Content
A22 Office chair
A22 Office desk
A22 Res. bed
A25 Res. bed
A25 Res. lamp
A26 Office chair
A26 Res. chair
A26 Res. bed
A26 Res. lamp
I want to produce the following output:
Building | Type | Content |
A22 | Office | chair, desk |
A22 | Res. | bed |
A25 | Res. | bed, lamp |
A26 | Office | chair, desk |
A26 | Res. | chair, bed, lamp |
Is there a way to do this in proc report, or should I merge the content cells in a data step?
Try something like the following, and then use the last (commented out right now) to keep only the last record.
proc sort data=have; by building type; run;
data want;
length content_all $200.;
set have;
by building type;
retain content_all;
if first.type then content_all=content;
else content_all=trim(content_all)|| ", "||trim(compress(content));
*If last.type then output;
run;
I feel better to do this in data step. ll giv you more handle on achieving conditional statement.
Thanks sascom10. I don't know how to do that though. Do you have an example code or paper you could share with me?
Try something like the following, and then use the last (commented out right now) to keep only the last record.
proc sort data=have; by building type; run;
data want;
length content_all $200.;
set have;
by building type;
retain content_all;
if first.type then content_all=content;
else content_all=trim(content_all)|| ", "||trim(compress(content));
*If last.type then output;
run;
Hi Reeza,
This was a great suggestion. I realized that there is another variable that is important. However, for some reason it is associating the wrong records to each other. For instance, building A22 will be associated with ID3. So the data looks like this:
ID Building Type Content
1 A22 Office chair
1 A22 Office desk
2 A22 Res. bed
2 A25 Res. bed
2 A25 Res. lamp
3 A26 Office chair
3 A26 Res. chair
4 A26 Res. bed
4 A26 Res. lamp
Then I added the ID to the code:
proc sort data=have; by building id type; run;
data want;
length content_all $200.;
set have;
by building id type;
retain content_all;
if first.type then content_all=content;
else content_all=trim(content_all)|| ", "||trim(compress(content));
*If last.type then output;
run;
I can't get everything to match up with the right ID as well.
That should work still, but you may need ID before building as you do in the data (BY ID Building type).
You'll need to post what your answer should be with the new variable to help figure out what's going wrong.
Hi Reeza,
I understand the problem now. Your solution is perfect. My data set is the problem. I have a couple of building id numbers that appear twice, but the second iteration has a trailing blank so that SAS thinks they are 2 separate ids. Do you have a solution for deleting trailing blanks?
Thanks,
Sarah
id=trim(compress(id));
Thanks, I possibly have 2 trailing blanks because I've been using that solution but it did not work. Also, for some of the ids there are no trailing blanks.
it should remove all trailing blanks regardless.
To see if that's the issue, run a proc freq and if they appear in different columns then they're different.
Another solution is to convert to numeric and then back to character. not sure if the following is correct, but that's the idea.
id=input(put(id, 8.)$8.);
Thanks, I'm not sure why it didn't work. I several if-then statements to recode the IDs that had blanks.
thanks.
Try this -
data test1;
input building $3. type $8. content $8.;
datalines;
A22 Office chair
A22 Office desk
A22 Res. bed
A25 Res. bed
A25 Res. lamp
A26 Office chair
A26 Res. chair
A26 Res. bed
A26 Res. lamp
run;
proc sort data=test1;
by building type content;
run;
proc transpose data=test1 out=test1_wide;
by building type;
var content;
run;
data test2;
length content $30.;
set test1_wide;
retain building type content;
content = trim(col1)||' '||trim(col2)||' '||trim(col3);
keep building type content;
run;
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.