Help using Base SAS procedures

Repeat group variables in proc report containing across

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Repeat group variables in proc report containing across

[ Edited ]

I know that this is widely asked question, but I have faced a situation where I couldn't get any workaround to work. I am by no means an expert on proc report since I find it non intuitive and cry myself to sleep almost every night because of this particular procedure. I would say the most common "problem" I face is the non existing option for repeating group values.

 

I have attached an example code which also contains links to the methods I have already tried unsuccesfully (commented). If someone could come up with a better approach or solution I would be in eternal dept for him/her.

I would also note that the reason I need to use proc report is that I have to have cell specific control over the output.

I am running the version 9.4.


Accepted Solutions
Solution
‎07-21-2016 05:21 AM
Super User
Posts: 10,018

Re: Repeat group variables in proc report containing across

[ Edited ]
data sample_data;
length var1 8 var2 $6  var3 $10 var4 $10 var5 $15;
input var1 var2 $ var3 $ var4 $ var5 $;
datalines;
1 12345 value1 var1 something
1 12345 value2 var2 something
1 12345 value3 var3 something
1 12345 value4 var4 something
1 12345 value5 var5 something
1 12346 value1 var1 something
1 12346 value2 var2 something
1 12346 value3 var3 something
1 12346 value4 var4 something
1 12347 value1 var1 something
1 12347 value2 var2 something
1 12347 value3 var3 something
1 12347 value4 var4 something
1 12348 value1 var1 something
1 12348 value2 var2 something
1 12348 value3 var3 something
1 12348 value4 var4 something
1 12348 value5 var5 something
1 12349 value3 var3 something
1 12349 value5 var5 something
2 54321 value1 var1 something
2 54321 value2 var2 something
2 54321 value3 var3 something
2 54321 value4 var4 something
2 54322 value1 var1 something
2 54322 value2 var2 something
2 54322 value3 var3 something
2 54322 value4 var4 something
2 54323 value1 var1 something
2 54323 value2 var2 something
2 54323 value3 var3 something
2 54323 value4 var4 something
2 54324 value1 var1 something
2 54324 value2 var2 something
2 54324 value3 var3 something
2 54325 value3 var3 something
2 54325 value4 var4 something
2 54325 value5 var5 something
2 54326 value1 var1 something
2 54326 value4 var4 something
;
run;

proc format; 
value var
1='format1'
2='format2';
run;

data sample_data;
set sample_data;
dummy=1;
run;

proc report data=sample_data out=testi nowd;
col var1 ('Title1' _var1) ('Title2' var2 ) (var3,var4),(var5) dummy;
define var1 /'' order=internal group format=var. noprint;
define _var1 /'' computed format=var.;
define var2 /'' group;
define var3 / across nozero missing order=internal;
define var4 /  across nozero;
define var5/'' missing;
define dummy/noprint;
compute _var1;
 if not missing(var1) then temp=var1;
 _var1=temp;
endcomp;
run;

 

x.png

View solution in original post


All Replies
Super User
Posts: 10,018

Re: Repeat group variables in proc report containing across

So what you are looking for ? Post a picture to show what you want .

Contributor
Posts: 60

Re: Repeat group variables in proc report containing across

I would like to have the exact output the sample code generates except the Title1 values would need to be repeated on every row (now they are grouped and only shown when a break occures.

Solution
‎07-21-2016 05:21 AM
Super User
Posts: 10,018

Re: Repeat group variables in proc report containing across

[ Edited ]
data sample_data;
length var1 8 var2 $6  var3 $10 var4 $10 var5 $15;
input var1 var2 $ var3 $ var4 $ var5 $;
datalines;
1 12345 value1 var1 something
1 12345 value2 var2 something
1 12345 value3 var3 something
1 12345 value4 var4 something
1 12345 value5 var5 something
1 12346 value1 var1 something
1 12346 value2 var2 something
1 12346 value3 var3 something
1 12346 value4 var4 something
1 12347 value1 var1 something
1 12347 value2 var2 something
1 12347 value3 var3 something
1 12347 value4 var4 something
1 12348 value1 var1 something
1 12348 value2 var2 something
1 12348 value3 var3 something
1 12348 value4 var4 something
1 12348 value5 var5 something
1 12349 value3 var3 something
1 12349 value5 var5 something
2 54321 value1 var1 something
2 54321 value2 var2 something
2 54321 value3 var3 something
2 54321 value4 var4 something
2 54322 value1 var1 something
2 54322 value2 var2 something
2 54322 value3 var3 something
2 54322 value4 var4 something
2 54323 value1 var1 something
2 54323 value2 var2 something
2 54323 value3 var3 something
2 54323 value4 var4 something
2 54324 value1 var1 something
2 54324 value2 var2 something
2 54324 value3 var3 something
2 54325 value3 var3 something
2 54325 value4 var4 something
2 54325 value5 var5 something
2 54326 value1 var1 something
2 54326 value4 var4 something
;
run;

proc format; 
value var
1='format1'
2='format2';
run;

data sample_data;
set sample_data;
dummy=1;
run;

proc report data=sample_data out=testi nowd;
col var1 ('Title1' _var1) ('Title2' var2 ) (var3,var4),(var5) dummy;
define var1 /'' order=internal group format=var. noprint;
define _var1 /'' computed format=var.;
define var2 /'' group;
define var3 / across nozero missing order=internal;
define var4 /  across nozero;
define var5/'' missing;
define dummy/noprint;
compute _var1;
 if not missing(var1) then temp=var1;
 _var1=temp;
endcomp;
run;

 

x.png

Contributor
Posts: 60

Re: Repeat group variables in proc report containing across

Thanks a lot! Could you please explain what the var2=var2 does on the col statement?

Super User
Posts: 10,018

Re: Repeat group variables in proc report containing across

Opps. It is superfluous. You could delete it . ==> ('Title2' var2 )
Super User
Super User
Posts: 7,942

Re: Repeat group variables in proc report containing across

Well, I tend to agree that report iis a bit difficult.  In my industry we have to validate, so go for a one step away approach.  Create a dataset which is nearly identical to the output you want, then have a simple report to output it.  This allows you to save the dataset for QC, and keep report simple.  I really don't like doing calculations, groupings and such like in a reporting procedure.  Set breaking up, grouping as additional variables, and you have all the necessary info in the dataset.

Contributor
Posts: 60

Re: Repeat group variables in proc report containing across

Well I  have previously used a transposed datasets to do just what RW9 describes, and probably I have to go that way on this case too. The reason I would like to use proc report to do the transposing is that the actual variable names (titles, in the example value1 etc.) varies from table to table and contains many nasty things like , / ' you name it. Also this proc report is generated partly with macro variables which spices things a bit more.

Especially the variable names causes problems since validvarname=any works, but those  'var'n are a bit messy to handle in macro variable world. All in all I am just trying to save myself from annoying data manipulation work.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 512 views
  • 1 like
  • 3 in conversation