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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

7 REPLIES 7
Ksharp
Super User

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

BobHope
Quartz | Level 8

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.

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

BobHope
Quartz | Level 8

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

Ksharp
Super User
Opps. It is superfluous. You could delete it . ==> ('Title2' var2 )
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

BobHope
Quartz | Level 8

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.

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 7 replies
  • 2031 views
  • 2 likes
  • 3 in conversation