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

Hi everyone,

 

I have the following dataset:

Product_ID Name
ID1 NAME1
ID1 NAME2
ID1 NAME3
ID2 NAME4
ID3 NAME5
ID3 NAME6
ID4 NAME7
ID5 NAME8
ID6 NAME9
ID7 NAME10


I'm trying to find a way to output/list the grouped Names for each Product ID in a dataset, like the table below:

 

Product_ID New Column
ID1 • NAME1
• NAME2
• NAME3
ID2 • NAME4
ID3 • NAME5
• NAME6
ID4 • NAME7
ID5 • NAME8
ID6 • NAME9
ID7 • NAME10

 

Do you have any suggestions how to achieve this?

 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

This works (note you need ods excel which in one of the newer releases - 9.3 onwards perhaps, can't remember):

data x;
  infile cards dlm="," ;
  input product_ID : $20. NAME : $60. ;
  cards; 
ID1,NAME1
ID1,NAME2
ID1,NAME3
ID2,NAME4
ID3,NAME5
ID3,NAME6
ID4,NAME7
ID5,NAME8
ID6,NAME9
ID7,NAME10
; 
run;

data have;
length temp $ 1024; 
set x;
by product_id;
if first.product_id then temp='';
retain temp;
  temp = catx('0D'x, temp, catx(" ", "^{unicode 25cf}",NAME) );
if last.product_id then output;
run;

ods noresults;
ods escapechar="^"; 
ods excel file="s:\temp\rob\test.xlsx";
proc report data=have nowd OUT=have2  split="/";  
  column product_id temp;
  define product_id / display; 
  define temp / display style(column)=[protectspecialchars=off cellheight=3cm cellwidth=2cm]; 
run;
ods excel close;

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Just an overview:

1) Add the bullet to your data (probably need to use the '07'x) e.g.:

data have;

  set have;

  name=cats('07'x,name);

run;

 

2) Use proc report to output your data, setting product_id as order variable and applying spanrows option:

proc report data=have spanrows;

  define product_id / order style(column)=[vjust=middle];

...

 

mjheever
Obsidian | Level 7

Hi RW9,

 

Thank you for help!

 

I've adjusted some of the code that give me the correct output:

data x;
  infile cards dlm="," ;
  input product_ID : $20. NAME : $60. ;
  cards; 
ID1,NAME1
ID1,NAME2
ID1,NAME3
ID2,NAME4
ID3,NAME5
ID3,NAME6
ID4,NAME7
ID5,NAME8
ID6,NAME9
ID7,NAME10
; 
run;

data have;
length temp $ 1024; 
set x;
by product_id;
if first.product_id then temp='';
retain temp;
  temp = catx("^{newline}", temp, catx(" ", "^{unicode 25cf}",NAME) );
if last.product_id then output;
run;

ods escapechar="^"; 

proc report data=have nowd OUT=have2  split="/";  
  column product_id temp;
  define product_id / display; 
  define temp / display; 
run;

But now I'm struggling to export the ODS output to Excel, since the bullet points are now exported into different rows and not into one row, is there any way around this?

 

Thank you!

 

 

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

This works (note you need ods excel which in one of the newer releases - 9.3 onwards perhaps, can't remember):

data x;
  infile cards dlm="," ;
  input product_ID : $20. NAME : $60. ;
  cards; 
ID1,NAME1
ID1,NAME2
ID1,NAME3
ID2,NAME4
ID3,NAME5
ID3,NAME6
ID4,NAME7
ID5,NAME8
ID6,NAME9
ID7,NAME10
; 
run;

data have;
length temp $ 1024; 
set x;
by product_id;
if first.product_id then temp='';
retain temp;
  temp = catx('0D'x, temp, catx(" ", "^{unicode 25cf}",NAME) );
if last.product_id then output;
run;

ods noresults;
ods escapechar="^"; 
ods excel file="s:\temp\rob\test.xlsx";
proc report data=have nowd OUT=have2  split="/";  
  column product_id temp;
  define product_id / display; 
  define temp / display style(column)=[protectspecialchars=off cellheight=3cm cellwidth=2cm]; 
run;
ods excel close;
mjheever
Obsidian | Level 7

Thank you @RW9 - working perfectly!

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
  • 4 replies
  • 2338 views
  • 0 likes
  • 2 in conversation