Nested column names - different sub column names per head column

[EvelienV]

Hi,

I'm trying to build the following output per ID:

I found this code in another thread:

proc report data=split5 nowd out=split6;
	column ('CGID' cgid) Theme1, theme2, count;
	define cgid / group '';
	define theme1 / across '';
	define theme2 / across '';
	define count / '';	
run; quit;

but when I run this, I get all the subcolumn names in every head column (so Product experience gets 9 subcolumns, Value for money gets 9 subcolumns and the same goes for Content).

Herewith some sample code:

data WORK.SPLIT5;
  infile datalines dsd truncover;
  input CGID:$7. Theme1:$150. Theme2:$150.;
  label CGID="CGID";
datalines;
2782189 Value for money Alignment with needs & expectations
2782189 Value for money Price transparancy
2782189 Product experience Speed
2782189 Product experience Search & filter options
2782189 Product experience Search results relevancy
2782189 Product experience Design
2782228 Content Quality of forms & tools (calculator, calendars)
2782228 Content Content quality
2782228 Value for money Price transparancy
2782231 Content Content quality
;;;;

I'd like it to be as flexible as possible, as new 'Theme 2' options might come up every month...

Does anyone know a fix for this?

Thanks in advance,

Evelien

[Vince_SAS]

Does this help?

data work.split5;
length c1-c9 8;
call missing (of _all_);
label c1 = 'Speed'
      c2 = 'Search results relevancy'
      c3 = 'Search filter options'
      c4 = 'Design'
      c5 = 'Alignment with needs and expectation'
      c6 = 'Price value received'
      c7 = 'Price transparency'
      c8 = 'Quality of forms and tools'
      c9 = 'Content quality';
run;

ods _all_ close;

ods Excel file='C:\temp\tenp.xlsx' options(flow='header');
  proc report data=work.split5;
    column ('Product Experience' c1-c4) ('Value for Money' c5-c7) ('Content' c8-c9);
  run; quit;
ods Excel close;

Vince DelGobbo

SAS R&D

[ballardw]

Examples of what your current data looks like would go far to providing working example code.

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.

I suspect that some form of reshaping your existing data is likely to be needed but the approach depends on what you have currently.

Possibly assigning your Theme2 values to three other variables and using fixed column headings as @Vince_SAS did for each of the single new variables.

[EvelienV]

Thanks for the feedback, I've edited my original question and added some of my data.

Next to this, I'd like to keep the column names flexible if possible, since new ones might come up every month and I'd rather not have to add these manually to the list...

[ballardw · Accepted solution]

---

@EvelienV wrote:

Hi,

I'm trying to build the following output per ID:

I found this code in another thread:

proc report data=split5 nowd out=split6;
	column ('CGID' cgid) Theme1, theme2, count;
	define cgid / group '';
	define theme1 / across '';
	define theme2 / across '';
	define count / '';	
run; quit;

but when I run this, I get all the subcolumn names in every head column (so Product experience gets 9 subcolumns, Value for money gets 9 subcolumns and the same goes for Content).

Herewith some sample code:

data WORK.SPLIT5;
  infile datalines dsd truncover;
  input CGID:$7. Theme1:$150. Theme2:$150.;
  label CGID="CGID";
datalines;
2782189 Value for money Alignment with needs & expectations
2782189 Value for money Price transparancy
2782189 Product experience Speed
2782189 Product experience Search & filter options
2782189 Product experience Search results relevancy
2782189 Product experience Design
2782228 Content Quality of forms & tools (calculator, calendars)
2782228 Content Content quality
2782228 Value for money Price transparancy
2782231 Content Content quality
;;;;

I'd like it to be as flexible as possible, as new 'Theme 2' options might come up every month...

Does anyone know a fix for this?

Thanks in advance,

Evelien

---

Please check that your data steps create the data you need. The output I get from printing that data set is:

From work.split5

Obs     CGID        Theme1      Theme2

  1    2782189
  2    2782189
  3    2782189
  4    2782189
  5    2782189
  6    2782189
  7    2782228    calendars)
  8    2782228
  9    2782228
 10    2782231

Consider:

data WORK.SPLIT5;
  infile datalines dlm='|' dsd truncover;
  input CGID:$7. Theme1:$150. Theme2:$150.;
  label CGID="CGID";
datalines;
2782189|Value for money|Alignment with needs & expectations
2782189|Value for money|Price transparancy
2782189|Product experience|Speed
2782189|Product experience|Search & filter options
2782189|Product experience|Search results relevancy
2782189|Product experience|Design
2782228|Content|Quality of forms & tools (calculator, calendars)
2782228|Content|Content quality
2782228|Value for money|Price transparancy
2782231|Content|Content quality
;;;;

For a basic count I think this comes pretty close to what you show.

data WORK.SPLIT5;
  infile datalines dlm='|' dsd truncover;
  input CGID:$7. Theme1:$150. Theme2:$150.;
  label CGID="CGID";
datalines;
2782189|Value for money|Alignment with needs & expectations
2782189|Value for money|Price transparancy
2782189|Product experience|Speed
2782189|Product experience|Search & filter options
2782189|Product experience|Search results relevancy
2782189|Product experience|Design
2782228|Content|Quality of forms & tools (calculator, calendars)
2782228|Content|Content quality
2782228|Value for money|Price transparancy
2782231|Content|Content quality
;;;;

proc sql;
   create table work.temp as 
   select Distinct theme2 as start, theme1 as label,'Themes' as fmtname,
          'C' as type
   from work.split5
   ;
quit;

proc format library=work cntlin=work.temp;
run;
data work.rep;
   set work.split5;
   themeheader=theme2;
run;


proc tabulate data=work.rep;
   class theme2 themeheader cgid;
   format themeheader $themes.;
   tables cgid=' ',
          themeheader=' '*theme2=' '*n=' '
          /misstext='0'
   ;
run;

But as soon as you throw other stuff in there I won't make any claims.

I think part of the issue is the core way that proc report builds columns and without adding lots of variables and perhaps significant macro coding to account for "new values" this is as much as I can' come up with at this time.

If you need compute blocks for things then you will need to provide more example data involving the other variables and what the output needs to be.